sql queries
TRANSCRIPT
![Page 1: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/1.jpg)
SQL Queries
![Page 2: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/2.jpg)
This PPT includes
Creating a database Creating a table Constraints Inserting values in the table Retrieving information from table Working with multiple table
![Page 3: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/3.jpg)
Introduction
Database Name: Institute Tables In database: 1) Batch_detail : batch_no, batch_name, trainer, duration
2) faculty_detail : trainer_id, trainer_name, qualification, experience, contact_no
3) student_detail: student_id, student_name,contact_no, batch_no, batch_name, address
![Page 4: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/4.jpg)
Creating a database
Syntax:
Create database database_name
E.g.
create database Institute;
![Page 5: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/5.jpg)
Creating a table
Syntax: create table table_name (col1_name,col2_name,…,coln_name)
E.g. create table batch_detail ( batch_no integer PRIMARY KEY, batch_name varchar UNIQUE, trainer varchar, batch-duration integer )
![Page 6: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/6.jpg)
Constraints
Constraints are used to limit the values entered into columns of a table.
Types of constraints 1) NOT NULL: Prevent a column from accepting NULL values. 2) UNIQUE: Ensures uniqueness of the values in a column. 3)PRIMARY KEY: Same as UNIQUE, but only one table per column is allowed. 4)CHECK: Controls the value of a column being inserted. 5) DEFAULT: Assign a default value for the column. 6) REFERENCES: Assign a Foreign Key constraint.
![Page 7: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/7.jpg)
Inserting data in to a table
Syntax: insert into table table_name Values(col1_val,col2_val,…..coln_val)E.g. insert into table batch_detail values( 01, ‘Testing_01’,’Rajiv Arora’,3)
*to insert multiple rows use this query for any no of times.
![Page 8: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/8.jpg)
Retrieving the information
Syntax: SELECT <column_list>
FROM <table_name
[WHERE <condition>]
[GROUP BY <column_name(s)>]
[HAVING<condition>]
[ORDER BY <expression>]
![Page 9: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/9.jpg)
WHERE Clause
WHERE Clause is used to specify the condition.
WHERE clause is case sensitive. Operators are used to specify the condition.
1)Relational Operators: =, >,<,<=,<=,<>,!=
2)Logical Operators: AND,OR, NOT
3)Special Operators: IN, BETWEEN,LIKE,IS
![Page 10: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/10.jpg)
Continue…
Syntax: select * from table_name; select col1_name,.. Coln_name from table_name; WHERE <condition>
e.g. select * from batch_detail WHERE Trainer =‘Rajiv Arora’ ;
Result: 1 testing_01 Rajiv Arora 3
![Page 11: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/11.jpg)
ORDER BY Clause
ORDER BY clause is used to impose an order on the result of a query.
It is used with SELECT stmt. One or more colums and/or expression can
be specified in ORDER BY clause.
![Page 12: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/12.jpg)
Continue…
Syntax:
SELECT <column_list>
FROM <table>
WHERE <condition>
ORDER BY<columns> [ASC|DESC] ;
![Page 13: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/13.jpg)
GROUP BY Clause
GROUP BY clause is used to divide the rows in a table into smaller groups.
SQL groups the query after it retrives the rows from a table.
Conditional Retrieval of rows from a grouped result is possible with the HAVING clause
![Page 14: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/14.jpg)
Continue…
Syntax: SELECT <column_list> FROM <table_name> GROUP BY <column(s) HAVING<condition> E.g. SELECT * FROM batch_detail GROUP BY batch_name HAVING trainer=‘Mr. Rajiv Arora’ ;
![Page 15: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/15.jpg)
Querying Multiple Tables
Collating Information(joins): Are used to combine columns from different table.
Types of Joins: 1) Equi Joins 2) Cartesian Joins 3) Outer Joins 4) Self Joins 5) Non-Equi Joins
![Page 16: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/16.jpg)
Continue….
Set Operators
1) Union
2) Intersect
3) Minus
![Page 17: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/17.jpg)
Equi Joins
When two tables are joined together using equality of values in one or more colums.
Syntax: SELECT colums_list FROM tab1,tab2 WHERE tab1.col_name = tab2.col_name ; E.g. : SELECT student_id, student_name,s.batch_no, batch_name, trainer FROM student_detail s, batch_detail b
WHERE s.batch_no = b.batch_no;
![Page 18: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/18.jpg)
Cartesian Joins
When no join condition clause is specifed in WHERE clause, each row of one table matches every row of the another table.This results in a cartesion product.
Syntax:
SELECT column_list FROM tab1,tab2; E.g.
SELECT student_id, student_name, batch_no, batch_name, trainer FROM student_detail , batch_detail ;
![Page 19: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/19.jpg)
Outer Joins
If there are any values in one table that do not have corresponding values in the other table, in an equi join that row will not be selected.Such rows can be selected forcefully by using the Outer join symbol(+).
Syntax: SELECT colums_list FROM tab1,tab2 WHERE tab1.col_name = tab2.col_name ; E.g. : SELECT student_id,student_name,s.batch_no, batch_name,trainer FROM student_detail s,batch_detail b
WHERE s.batch_no (+) = b.batch_no;
![Page 20: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/20.jpg)
Self Joins
To join a table to itself means that each row of the table is combined with itself and with every other row of a table.
This can be viewed as a join of two copies of the same table.
Syntax:
SELECT column_list FROM tab1,tab2;;
![Page 21: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/21.jpg)
SET Operators
UNION: This clause merges the outputs of two or more quaries into a single set of rows & columns.
Syntax: SELECT <stmt1> UNION SELECT <stmt2> E.g. SELECT student_name FROM student_detail WHERE batch_no = 01 UNION SELECT student_name FROM student_detail WHERE batch_no = 02
![Page 22: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/22.jpg)
Continue…
INTERSECT: This operator returns the rows that are common between two sets of rows.
Syntax: SELECT <stmt1> INTERSECT SELECT <stmt2> E.g. SELECT student_name FROM student_detail WHERE batch_no = 01 UNION SELECT student_name FROM student_detail WHERE trainer = ‘Rajiv Arora’
![Page 23: Sql queries](https://reader031.vdocuments.us/reader031/viewer/2022012318/558fc98a1a28ab9b198b464c/html5/thumbnails/23.jpg)