EXPERIMENT NO: 2 (A)
Aim :- To study and implement commands under Data Definition
Language and apply different types of constraints.
Syntax:
CREATE TABLE <relation_name/table_name >
(field_1data_type(size),field_2data_type(size),);
Example:
create table student12(
name char (25),
marks int,
email_id char (25));
Output:
The TRUNCATE
Syntax:
TRUNCATE TABLE <Table_name>
Example
TRUNCATE TABLE student12;
Output
DROP TABLE
Syntax:
DROP TABLE relation_name;
Example: DROP TABLE student12;
Output:
CONSTRAINTS
NOT NULL
Syntax:
CREATE TABLE Table_Name (column_name data_type (size) NOT
NULL, );
Example:
CREATE TABLE student12(
name char (25) NOT NULL
);
Output:
UNIQUE
Syntax:
CREATE TABLE Table_Name(
column_name data_type(size) UNIQUE, ….);
Example:
CREATE TABLE student11 (name char(25) UNIQUE);
Output:
PRIMARY KEY: A field which is used to identify a record
uniquely.
Syntax:
CREATE TABLE Table_Name(column_name data_type(size)
PRIMARY KEY,
….);
Example:
CREATE TABLE student101(
name char(25) PRIMARY KEY
);
Output:
Conclusion: Successfully studied and implemented commands under
Data Definition Language and Implemented different types of
constraints in sql.
EXPERIMENT NO :2(B)
AIM: To study and implement commands under Data Manipulation
Language.
INSERT INTO: This is used to add records into a relation. These are
three type of
INSERT INTO queries which are as
a) Inserting a single record
Syntax:
INSERT INTO <relation/table name>
(field_1,field_2……field_n)VALUES
(data_1,data_2,........data_n);
Example:Insert into student112(name,marks,email_id)
Values('Raj',21,'raj20@gmail.com');
Insert into student112 (name,marks,email_id)
Values(‘Rohn',54,'rohn21@gmail.com');
Insert into student112 (name,marks,email_id)
Values('Abhay’,23,'abhay4116@gmail.com');
Output:
UPDATE-SET-WHERE:
Syntax:
SQL>UPDATE relation name SET
Field_name1=data,field_name2=data,
WHERE field_name=data;
Example: update student52 set marks=80;
Output:
To Retrieve data from one or more tables.
SELECT FROM
Syntax: SELECT a set of fields FROM relation_name;
Example: select name,marks from student52;
Output:
SELECT - FROM -WHERE
Syntax: SELECT a set of fields FROM relation_name WHERE
condition;
Example: select * from student72 where marks=54;
Output:
Conclusion: Successfully studied and implemented sql commands
under Data Manipulation language
EXPERIMENT NO: 3
AIM: To study and implement Set Operations, string function and
operators.
Example of UNION:
The First table,
ID Name
1 abhi
2 adam
The Second table,
ID Name
2 adam
3 Chester
Syntax:
select * from First
UNION
select * from second
Output:
Union All
Example of Union All
The First table,
ID NAME
1 abhi
2 adam
The Second table,
ID NAME
2 adam
3 Chester
Union All query will be like,
select * from First
UNION ALL
select * from second
Output:
COMPARISON OPERATORS:
TABLE CODE
create table suppliers (
supplier_idnumber(10),
supplier_name char(100),
city char(100),
state char(100));
INSERT INTO suppliers VALUES
(100,'Microsoft','Redmond','Washington');
INSERT INTO suppliers VALUES (200,'Google','Mountain
View','California');
INSERT INTO suppliers VALUES (300,'Oracle','Redwood
City','California');
INSERT INTO suppliers VALUES (400,'Kimberly-
Clark','Irving','Texas');
Output:
Equality Operator (=)
SELECT *
FROM suppliers
WHERE supplier_name = 'Microsoft';
Output:
Inequality Operator ( !=/ <>)
SELECT *
FROM suppliers
WHERE supplier_name != 'Microsoft';
Output:
LOGICAL OPERATORS:
TABLE CODE:-
CREATE TABLE EMPLOYEE (
emp_id number(20),
emp_name char(20),
Email char(100),
Address char(200));
INSERT INTO EMPLOYEE values( 1 , 'ravi'
,'ravisharma@gmail.com' , ' bea avenue ');
INSERT INTO EMPLOYEE values( 2 , 'raj' , 'rajsangvi@gmail.com'
, 'twenth street');
INSERT INTO EMPLOYEE values( 3 , 'ankit'
,'ankitsangvi@gmail.com' , 'holam street');
INSERT INTO EMPLOYEE values( 4 , 'ankush'
,'ankushsawnani@gmail.com' , 'wall street');
Output:
AND Operator:
SELECT * FROM EMPLOYEE
WHERE emp_id = 2 AND Address = 'twenth street';
Output:
NOT Operator
SELECT * FROM EMPLOYEE
WHERE NOT emp_id = 1;
Output:
SPECIAL OPERATOR:
BETWEEN
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2
Example:
SELECT EMP_ID
FROM EMPLOYEE
WHERE EMP_ID BETWEEN 2 AND 4
Output:
LIKE Operator:
‘r%’
SELECT * FROM EMPLOYEE
WHERE emp_name LIKE ‘r%’;
Output:
Conclusion: Successfully implemented Set Operations, string function
and operators.
EXPERIMENT NO: 4(A)
AIM: To implement Join Queries.
Syntax:
SELECT column 1, column 2, column 3...
FROM table_name1, table_name2
Simple Join(INNER JOIN)
Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
TABLE CODE: Ceate table suppliers1(
suppliers_id int,suppliers_name varchar(200));
insert into suppliers1 values(10000,'IBM');
insert into suppliers1 values(10001,'INTEL');
insert into suppliers1 values(10002,'MiCRO');
insert into suppliers1 values(10003,'VICTUS');
Output:
create table orders2 ( order_id int,supplier_id int,order_date int );
insert into orders2 values(500125,10000,2003/05/12);
insert into orders2 values(500126,10001,2003/05/13);
insert into orders2 values(500127,10004,2003/05/14);
Output:
Inner join:
SELECT suppliers1.supplier_id, suppliers1.supplier_name,
orders2.order_date
FROM suppliers1
INNER JOIN orders2
ON suppliers1.supplier_id = orders2.supplier_id;
Output:
Left outer join:
SELECT suppliers1.supplier_id, suppliers1.supplier_name,
orders2.order_date
FROM suppliers1
LEFT OUTER JOIN orders2
ON suppliers1.supplier_id = orders2.supplier_id
Output:
Right outer join:
SELECT orders2.order_id, orders2.order_date,
suppliers1.supplier_name
FROM suppliers1
RIGHT OUTER JOIN orders2
ON suppliers1.supplier_id = orders2.supplier_id;
Output:
Full outer join:
SELECT suppliers1.supplier_id, suppliers1.supplier_name,
orders2.order_date
FROM suppliers1
FULL OUTER JOIN orders2
ON suppliers1.supplier_id = orders2.supplier_id;
Output:
Conclusion: Successfully implemented Join Queries.
EXPERIMENT NO: 4(B)
AIM: To Implement Basic and Nested complex SQL queries.
Sub query
Syntax:
operandcomparison_operator ANY (subquery)
operand IN (subquery)
operandcomparison_operator SOME (subquery)
Where comparison_operator is one of these operators: = ><>= <= <>
!=
OUTPUT:-
TABLE CODE
CREATE TABLE student (StudentID Varchar(10),Name char(20));
CREATE TABLE marks (StudentID Varchar(10),Total_marks
number(20));
INSERT INTO student VALUES('V001','Abe');
INSERT INTO student VALUES('V002','Abhay');
INSERT INTO student VALUES('V003','Acelin');
INSERT INTO student VALUES('V004','Adelphos');
INSERT INTO marks VALUES ('V001',95);
INSERT INTO marks VALUES ('V002',80);
INSERT INTO marks VALUES ('V003',74);
INSERT INTO marks VALUES ('V004',81);
SUBQUERY
SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid AND b.total_marks >
(SELECT total_marks
FROM marks
WHERE studentid = 'V002');
Output:
Conclusion: Successfully Implemented Nested & Complex Queries
EXPERIMENT NO: 5
AIM: Implementation of different types of Aggregation Functions,
Group by, Order by & Having Clause
CREATE TABLE:
create table Student ( Student_Id int, Name char(20), Marks int, Subject
char(20));
insert into Student values(1,'Ravi',35, 'DBMS');
insert into Student values(2,'Taraka',32,'DSA');
insert into Student values(3,'Priyanka',40, 'DBMS');
AGGREGATION FUNCTIONS
Count.
Syntax: COUNT (Column name)
Example: SELECT COUNT(Subject) FROM Student WHERE
Subject = 'DBMS';
Output:
MIN
Syntax: MIN (Column name)
Example: SELECT MIN(Marks) FROM Student;
Output:
GROUP BY-HAVING:
Syntax:
SELECT column_name, aggregate_function(column_name) FROM
table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
Example:
SELECT COUNT (Marks) FROM Student GROUP BY Marks
HAVING Marks > 10;
Output:
Conclusion: Successfully Implemented different types of
Aggregation Functions, Group by, Order by & Having Clause.
EXPERIMENT NO: 6
AIM: To Study & Implements The VIEW & Trigger
View
Syntax: CREATE VIEW <view_name> AS SELECT <set of fields>
FROM relation_name WHERE (Condition)
Example: Create view MY_VIEW as select name , age from
customers where age>=25 with check option;
Output:
Updating a view
Syntax : CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Example: Update customers_view set age=35 where name=’Ramesh’;
Output:
Creating Triggers
Syntax:
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Example:
Triggering a Trigger
Example:
INSERT INTO CUSTOMERS
(ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );
UPDATE customers
SET salary = salary + 500
WHERE id = 2;
When a record is updated in CUSTOMERS table, above create trigger
display_salary_changes will be fired and it will display the following
result:
Old salary: 1500
New salary: 2000
Salary difference: 500
Output:-
CREATE VIEW
CREATE VIEW CUSTOMERS_VIEW AS SELECT name, age
FROM CUSTOMERS;
Output:
DELETE VIEW
DELETE FROM CUSTOMERS_VIEW
WHERE age = 22;
Output:
Conclusion: Successfully Studied and Implemented View & Trigger.
EXPERIMENT NO:7(A)
AIM: To implement Database Connectivity.
