AIM : Design database and create tables for example student, employee etc.

THEORY :
Database : A database is an organized collection of structured information, or
data, typically stored electronically in a computer system.
Table : A table is a collection of related data held in a table format within a
database. It consists of columns and rows.
Commands :
1): ‘Create’ command : it is used to create a table or database.

Syntax :
create table table_name
( Column1 datatype,
Column2 datatype,
Column3 datatype
);

Query:
create table employee
( emp_id integer,
emp_name char (20),
emp_salary integer,
emp_grade char(1)
);

create table student

( enNo integer,
name char(20),
gender char(1),
grade char(1),
phone integer
);
2): ‘Describe’ command : This command show the structure of table
which include name of the column, datatype, size of column etc.

Syntax :
Describe table_name;
or
Desc table table_name;

3): ’Insert’ command : It is used to insert values of data to be stored in

table.

Syntax :
insert into table_name (column1, column2, column3, …)
values (val1, val2, val3, …);

Query :
insert into student(enNo, name, gender, grade, phone)
values(104,’Anurag’,’M’,’a’,8852851);

insert into employee(emp_id, emp_name, emp_salary, emp_grade)

values(1515,’Srinivas’,8000,’A’);

4): ’Select’ command : it’s used to retrieve or fetch data from database.
We can fetch either the entire table or according to some specific rule.

Syntax:
(1). select * From table_name;
(2). select * From table_name where col_4=102;
(3). select col_1, col_2 from table_name;
(4). select col_1, col_2 from table_name where col_3>500;

Query :
select * from student;

select * from employee;

1. Creating new table student

2. Inserting values into table student

3. Viewing all records using Select command.

4. Viewing specific record and also specific column using select command

5. Creating new table employee

6. Inserting values into employee table.

7. Viewing all records of table employee

AIM : Apply different constraints of SQL to Tables.
THEORY :
Constraints : Constraints are set of rules that are applied to tables.
Types of Constraints :
(1) Not Null constraint : By default, a column can hold null values this constraint
enforces a column to not accept null values.
Syntax:
fieldname datatype NOT NULL

(2) Unique Constraint : This constraint ensures that all values in a column are
unique.
Syntax:
fieldname datatype NOT NULL UNIQUE

(3) Primary Key Constraint : This Constraint uniquely identify each record in a
table. Key must contain unique values and can’t contains null values.
Syntax:
fieldname datatype NOT NULL,
PRIMARY KEY (fieldname),

(4) Default Constraint : use to provide a default value to column. Default value
will be added to all new records if not specified.
Syntax:
fieldname datatype DEFAULT ‘b’

(5) Check Constraint : It is used to limit value range that can be placed in
column.
Syntax:
fieldname datatype check(fieldname>18),

(6) Foreign Key Constraint : when key/Field in a table playing role of primary
key and in another table, it is non primary key then non primary key will be
called as foreign key.
Syntax:
foreign key (fieldname)
references table_name(fieldname)
QUERIES :

• create table persons

( personid integer PRIMARY KEY,
firstname char(20) NOT NULL,
lastname char(20),
age integer CHECK(age>19),
grade char(1) DEFAULT ‘B’
);

• create table orders

( orderid integer PRIMARY KEY,
orderno integer NOT NULL,
Personid integer,
FOREIGN KEY (person id) REFERENCES PERSON(person id)
);
1. Creating new table named 'persons'

2. inserting new record in table

3. checking 'unique' constraint

4. Error because first name can’t be null.

5. checking 'check' constraint

6. Viewing all records of table 'persons'. 7. Creating new table 'orders'.

8. Checking if duplicate values allowed in primary key constraint column.

9.View all records of table orders.

 AIM : Write a SQL statement for implementing Alter, Update & Delete
commands.

THEORY :
COMMANDS :
1) : Alter Command :
a. This command is used to add, delete, or modify column in existing table.
b. It is also used to add and remove constraints and rename column or table
name.
c. It is also used to modify data type and data type length.

Syntax :

• alter table Table_name

add column_name datatype(length);
• alter table Table_name
drop fieldname;
• alter table table_name
modify fieldname varchar (10);
• alter table table_name
rename column fieldname to newname;
• alter table table_name
rename to newname;
• alter table table_name
add primary Key(fieldname);

Queries :

• alter table student add age int;

• alter table student
 drop age;
• alter table student
modify enNo varchar(10);
• alter table student
rename column enNo to rollno;
• alter table student
rename to student1;

Note: It is part of DDL

2) : Update Command :

A. This command is used to modify existing records in a table.

B. Where clause can be used to update query to update selective rows
otherwise all rows would be affected.

Syntax :
update table_name
set column_name=value
where [condition];

Queries :
• update student
set grade ‘B’
where rollno=101;
• update employee
set emp_salary=emp_salary+1000 and emp_grade=’A’
where emp_salary<5000;

3): Delete Command :

A. This is used to delete the existing records from a table.
B. Delete quarry can be used to with 'where' clause to delete selective row
otherwise all the rows will be deleted.
Syntax :
Delete from table_name
where [condition];

Query :
Delete from student
Where rollno=101;
1. use alter command to add new attribute in student table

2. use alter command to delete 3. use to changes attribute properties

4. update command to update values 5. delete command to delete values

6. Final student table

7. Final employee table

❖ SQL
SQL is a short-form of the structured query language, and it is pronounced as S-Q-
L or sometimes as See-Quell. SQL stands for Structured Query Language. It is used
for storing and managing data in relational database management system (RDMS).
It is a standard language for Relational Database System. It enables a user to create,
read, update and delete relational databases and tables. All the RDBMS like
MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard
database language. SQL allows users to query the database in a number of ways,
using English-like statements. SQL is an ANSI (American National Standard
Institute) standards.

❖ SQL Softwares

• Microsoft SQL Server Management Studio (SSMS)

• SQL Gate
• MySQL
• Pop SQL.
• PostgreSQL

❖ Applications
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database

❖ RDBMS (Relation Database management system)

It is basis for SQL and for all modern database system such as MySQL server,
IBM DB2, Oracle MySQL and MS Access. The data in RDBMS is collection of
related data entries and it consist of columns and rows.
• Database Table: a database table most often contains one or more
tables. Each table is identified by a name. Table contains records with
data.
• SQL Statement: Most of actions you need to perform on a database
done with SQL statement following SQL statement select all the
records is the “Customer” table;
❖ Most important Command of SQL
• Select: extract data from database.
• Update: update data to database.
• Delete: Delete data from database.
• Insert into: insert new records in database.
• Create Database: for creating new database.
• Alter Database: Modifies database
• Create Table: create a new table.
• Drop Table: Delete existing table in the database.
• Alter Table: modifies existing table in the database.
• Create index: create a new index (search key)
• Drop Index: delete an index.

❖ Data Definition Language (DDL)

• DDL changes the structure of the table like creating a table, deleting a table, altering a
table, etc.
• All the command of DDL are auto-committed that means it permanently save all the
changes in the database.
Here are some commands that come under DDL :
• CREATE
• ALTER
• DROP
• TRUNCATE.

❖ Data Manipulation Language (DML)

• DML commands are used to modify the database. It is responsible for all form of changes
in the database.
• The command of DML is not auto-committed that means it can't permanently save all the
changes in the database. They can be rollback.
Here are some commands that come under DML :
• INSERT
• UPDATE
• DELETE

❖ Data Control Language (DCL)

• DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:
• Grant
• Revoke

❖ View Definition
It includes commands for defining views.
❖ Transaction Control
It includes commands for specifying beginning and ending along with commands to
have a control over transaction procession.

❖ Authorization
It includes commands for specifying access right to relation and views.

❖ Integrity
Integrity is ensuring that the logical and physical data stored in SQL Server are
structurally sound and consistent.
AIM : Write SQL statements for implementing functions MAX(), MIN(),
SUM(), AVG(), COUNT().

THEORY :
FUNCTIONS :

1) MAX() Function

The MAX() function returns the largest value of the selected column.

Syntax :
SELECT MAX(column_name)
FROM table_name
WHERE condition;
Query :
Select max(bonus) from employee_72;

2) MIN() Function

The MIN() function returns the smallest value of the selected column.

Syntax :

SELECT MIN(column_name)
FROM table_name
WHERE condition;

Query :

Select min(salary) from employee_72 where bonus<500000;

3) AVG() Function

The AVG() function returns the average value of a numeric column.

Syntax :

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Query :

Select avg(salary) from employee_72 where bonus>5000;

4) COUNT() Function

The COUNT() function returns the number of rows that matches a specified criterion.

Syntax :

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Query :

Select count(eno) from employee_72;

5) SUM() Function

The SUM() function returns the total sum of a numeric column.

Syntax :

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Query :

• Select sum(bonus) from employee_72 where salary<100000;

• Select sum(bonus) from employee_72 where salary<210000;
 1) Creating Table Employee_72.

2.) Inserting Values in Employee_72 and viewing all records.

3.) Using MAX() Function to show maximum bonus in Employee_72.

4) Using AVG() and MIN() Functions in Employee_72.

5) Using SUM() and COUNT() Functions in Employee_72.