Scribd
Upload
15 views24 pages

SQL Commands and Constraints Overview

The document describes experiments conducted related to SQL commands. It covers creating tables and applying constraints like primary key, unique, not null. It also covers data manipulation commands like insert, update, delete and select. Joins, aggregation functions, group by, order by and having clause are implemented. Set operations, comparison operators and string functions are also studied. Views and triggers are implemented to update and manipulate data in tables.

Uploaded by

adishree jalgaonkar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views24 pages

SQL Commands and Constraints Overview

The document describes experiments conducted related to SQL commands. It covers creating tables and applying constraints like primary key, unique, not null. It also covers data manipulation commands like insert, update, delete and select. Joins, aggregation functions, group by, order by and having clause are implemented. Set operations, comparison operators and string functions are also studied. Views and triggers are implemented to update and manipulate data in tables.

Uploaded by

adishree jalgaonkar
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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@[Link]');
Insert into student112 (name,marks,email_id)
Values(‘Rohn',54,'rohn21@[Link]');
Insert into student112 (name,marks,email_id)
Values('Abhay’,23,'abhay4116@[Link]');
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@[Link]' , ' bea avenue ');

INSERT INTO EMPLOYEE values( 2 , 'raj' , 'rajsangvi@[Link]'


, 'twenth street');

INSERT INTO EMPLOYEE values( 3 , 'ankit'


,'ankitsangvi@[Link]' , 'holam street');

INSERT INTO EMPLOYEE values( 4 , 'ankush'


,'ankushsawnani@[Link]' , '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 [Link] = [Link];
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 [Link], [Link], b.total_marks
FROM student a, marks b
WHERE [Link] = [Link] 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.

Common questions

Powered by AI

INNER JOIN returns only the rows that have matching values in both tables. LEFT OUTER JOIN returns all rows from the left table and the matched rows from the right table, or NULL if there is no match. RIGHT OUTER JOIN returns all rows from the right table and the matched rows from the left table, or NULL if there's no match. FULL OUTER JOIN returns all rows when there is a match in either left or right table records, filling in NULLs for missing matches from each side .

DELETE is used to remove rows from a table based on a condition and can be rolled back. TRUNCATE removes all rows in a table without logging individual row deletions and cannot be rolled back, essentially resetting it but retaining the structure. DROP TABLE deletes the entire table structure and contents, and cannot be rolled back even with transaction control .

Set operations like UNION and UNION ALL are used to combine the results of two or more SELECT queries. UNION removes duplicate rows from the combined result set, whereas UNION ALL includes all rows, including duplicates, making it faster. UNION is preferable when a unique list is needed, while UNION ALL is used for performance boosting when duplicates are acceptable and need to be retained, such as combining logs from different sources where log duplicates exist naturally .

INSERT INTO statement variants in SQL include inserting single records or multiple records at once, offering flexibility. The standard form (INSERT INTO … VALUES) is typically used for adding single entries, beneficial in applications where user-driven data input happens frequently. For bulk record additions, batch INSERTS reduce the transaction overhead compared to individual entries. However, batch inserts can complicate error handling, requiring detailed logging or transactions to ensure data integrity in case of failure .

NOT NULL ensures that a column cannot have a NULL value, which prevents missing data. UNIQUE ensures that all values in a column are different, preventing duplicate entries. PRIMARY KEY uniquely identifies each record in a table, enforcing entity integrity by combining the properties of NOT NULL and UNIQUE for the designated field .

A SQL VIEW simplifies database complexity by providing a virtual table representing the result of a database query. It encapsulates SQL logic and presents it as a single table to the end-user, enhancing security by restricting access to underlying tables, and simplifying query recoding. Potential drawbacks include performance overhead and the limitation of some updates on non-updatable views. A view can be updated if it has been defined with the WITH CHECK OPTION clause ensuring any update reflects valid data, like CREATE OR REPLACE VIEW customers_view AS SELECT name, age FROM customers WHERE age >= 25 .

Nested SQL queries, or subqueries, allow a query to be used as a condition in the main query. They can solve complex data retrieval tasks by enabling calculations to occur at multiple levels, such as calculating aggregates or filtering results based on dynamically computed values. Benefits include breaking down complex tasks into manageable parts and improving code readability and maintenance .

SQL logical operators, such as AND, OR, and NOT, are used in conditional statements to filter records based on multiple criteria. AND operator combines two or more conditions and returns records that meet all conditions. NOT negates a condition, returning records that do not meet the specified condition. For example, SELECT * FROM EMPLOYEE WHERE emp_id = 2 AND Address = 'twenth street' retrieves employees only where both conditions are true; SELECT * FROM EMPLOYEE WHERE NOT emp_id = 1 excludes records where emp_id is 1 .

Aggregation functions such as COUNT and MIN are used to perform calculations on multiple rows of a table’s column and return a single value. COUNT returns the number of rows that match the query criteria, while MIN returns the smallest value in a selected column. GROUP BY groups rows that have the same values in specified columns into summary rows, and HAVING is used to filter groups based on aggregate properties, providing precise control over how data is summarized and reported .

SQL triggers are special procedures that automatically execute on events like INSERT, UPDATE, or DELETE. They are used to maintain integrity and consistency, enforce security measures, and provide automated auditing capabilities. In a salary management system, a trigger could automatically log the old and new salary of an employee whenever a salary update action occurs, ensuring transparency and maintaining a record of salary changes .

You might also like