SQL Sort Notes

By Shiva Adasule(ENTC Engg.)
1

 Introduction to SQL
 SQL data types
 SQL Commands
 SQL Clauses, basic commands and Constraints
 SQL Joints
 SQL Queries Types
 SQL Indexes
 SQL Procedure
 SQL Cursors
 SQL View
 SQL Triggers
2

 SQL is a standard language for accessing and manipulating
databases.
 SQL used for RDBMS(Relational Database Management
System)
 The data in RDBMS is stored in database objects called tables.
A table is a collection of related data entries and it consists of
columns and rows.
 All modern DBMS use SQL
1. MS SQL Server
2. MySQL
3. IBM DB2
4. Oracle
5. Microsoft Access etc.
3

5
 Data Definition
Language (DDL)
 Data Manipulation
Language(DML)
 Data Control
Language(DCL)
 Transaction Control
Language(TCL)
 Data Query
Language(DQL)

 CREATE – use to create something
CREATE <Type> . . . ;
 DROP – used to delete something
DROP <type> . . . ;
 ALTER –used to change, rename, modify, alter
something.
ALTER <Type> <name> <OP> . . . ;
 TURNCATE – Delete something permanently
TRUNCATE <Type> . . . ;
6

 Insert - used to insert data into table
INSERT INTO <table> (<cols>)
VALUES (<cols_values>);
 Delete – used to delete data from table
DELETE FROM <table> . . . ;
 Update – used to data in table
UPDATE <table> SET <cols> = <value> . . . ;
7

 Grant – Used to provide any user access
privileges or other privileges for the
database.
GRANT <permissions> TO <users> ;
 Revoke - Used to take back permissions from
any user.
REVOKE <permissions> FROM <user> ;
8

 Commit - used to permanently save any
transaction into the database.
COMMIT;
 Savepoint - used to temporarily save a
transaction so that you can rollback to that point
whenever required.
SAVEPOINT <name>;
 Rollback - used with SAVEPOINT command to
jump to a savepoint in an ongoing transaction.
ROLLBACK TO <name>;
9

 SELECT – used to Give output of any query .
SELECT <cols_or_operation>
FROM <table_or_something_else> . . . ;
10

 Where - it use to filtering of table before group
by statement.
SELECT <something>
FROM <some_table>
WHERE <condition>;
 Having – it used to filtering of table after group
by statement.
SELECT <something>
FROM <some_table>
GROUP BY <cols>
HAVING <condition>;
11

 Arithmetical operation -
+,-,/,%,*
 Logical operations –
AND(&),OR(|),NOT
 Relational Operations –
<,>,<=,>=,= or <=> ,<>
 IN – exact checking
 BETWEEN – inclusive range checking
 LIKE – pattern checking
1. _ - used for one missing values
2. % - used for multiple missing values
12

 Order by – used for shorting (default is asc)
SELECT <something>
FROM <some_table>
ORDER BY <col1> , <col2> desc, . . ;
 Limit – used to select limited rows
SELECT <something>
FROM <some_table>
LIMIT <offset>,<row_count>;
 IF – if condition
IF(<condition>,<if_true>,<if_false>)
13

 CASE – same as switch
CASE (<var>)
WHEN <value> THEN <do>
WHEN <value> THEN <do>
. .
ELSE <do>
END
OR
CASE
WHEN <condition> THEN <do>
WHEN <condition> THEN <do>
. .
ELSE <do>
END
14

 Group Functions
◦ SUM()
◦ MIN()
◦ MAX()
◦ AVG()
◦ COUNT() ,etc
 Group By - used to make group of elements
SELECT <something>
FROM <some_table>
GROUP BY <col1>,<col2> ,..;
15

 NOT NULL – used for avoid null values
 UNIQUE – used to avoid same values
 CHECK – used to check some condition
 DEFAULT <value> - used to set default value
16

 A primary key is column or set of columns that uniquely
identified each row in a table
 A primary key must contain unique and not null values
 A composite primary key can have multiple columns whose
combination must always be unique
 To create primary key
CREATE TABLE <name>(
<col> PRIMARY KEY ,..);
OR
CREATE TABLE <name>(
<col>,..,PRIMARY KEY(<col_name>..) );
After table created
ALTER TABLE <table> ADD PRIMARY KEY(<cols>..);
 To Drop primary key
ALTER TABLE DROP PRIMARY KEY;
17

 Foreign key is used to maintain referential integrity between two
tables it points to the primary key of another table
 Foreign key constrain denoted a parent child relationship . A
child can’t be exist and parent can’t delete if child is exists.
 Foreign key can be set to NULL
 To Create Foreign Key
ALTER TABLE <table> ADD FOREIGN KEY <col_name>
REFERENCES <p_table>(<p_col>) [on_delete_action]
[on_update_action] ;
 To Delete Foreign Key
ALTER TABLE <table> DROP FOREIGN KEY;
18

Action Option Description
ON DELETE CASCADE If parent get delete,
the child also get
delete
SET NULL If parent get delete,
the child set to null
RESTRICT Delete is not possible
if child exists
ON UPDATE CASCADE If parent get updated,
the child also get
updated
SET NULL If parent get updated,
the child set to null
RESTRICT update is not possible
if child exists
19

 Cross Join – it take all combinations of both table rows
SELECT *
FROM <table1> JOIN <table2> ;
OR
SELECT *
FROM <table1> CROSS JOIN <table2>;
OR
SELECT *
FROM <table1>,<table2>;
 Natural Join – if both table have one same column then it
table reference that column join both tables and that same
columns comes ones in table.
SELECT *
FROM <table1> NATURAL JOIN <table2> ;
20

 Simple Query – One SELECT in the Query.
 Multiple Subqueries – it follows bottom to up
approach.
i.e.
SELECT *
FROM company
WHERE salary > (
SELECT AVG(salary)
FROM company
);
22

 Correlated Subqueries – here for every row of
main query the subqueries is are execute.
i.e.
SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
from employees
where dptid=e.dptid
)
23

 Index are used by query to find data from
table quickly
 If there are no index to help query then query
engine perform entire table scans
 To Create index use
CREATE INDEX <idx_name> ON
<table_name>(<col_name>);
 To Delete index
DROP INDEX <idx_name> ON <table_name>;
24

 Clustered Index – Those indexes are change
the order of the table in sorted order, there is
only one clustered index in the table which is
primary key.
 Secondary Index – all index created by user
are secondary indexes which stored in
separate storage area.
Note : When we make any column UNIQUE then
it create index for that column.
25

 A stored procedures is a segment of declarative
SQL statements stored inside the database
catalog.
 Stored procedures performance, reduce network
traffic, provide code reusability & Security.
 Stored Procedure are create using
CREATE PROCEDURE <prs_name> (<paramenters>)
BEGIN
[variable declaration]
[perform some operations]
END
26

 Variable can be type of IN,OUT,INOUT
 Example: sql code to find prime number
DELIMITER $$
CREATE PROCEDURE is_prime(IN num int)
BEGIN
DECLARE c int;
DECLARE x int;
SET c = 2;
SET x = 0;
my_loop:WHILE num >= c*c DO
IF num%c = 0 THEN
SET x = 1;
LEAVE my_loop;
END IF;
SET c = c+1;
END WHILE my_loop;
IF x = 1 THEN
SELECT “ITS COMPOSITE NUMBER”;
ELSE
SELECT “ITS PRIME NUMBER”;
END IF;
END $$
27

 A cursor allows to iterate set of rows returned
by a query and process accordingly
 We can create cursor inside the procedure to
handle result of set returned by a query
 MySQL cursors are read only and not
scrollable(mean one row at a time and one by
one step wise).
28

 Steps to working with cursors
1) DECELARE <cursor_name>
FOR <Select_statement>;
2) OPEN <cursor_name>;
3) FETCH <cursor_name> into <cols>.. ;
4) CLOSE <cursor_name>;
29

 Example of Cursor
30
delimiter $$
CREATE OR REPLACE PROCEDURE pro_agent()
BEGIN
DECLARE break int DEFAULT 0;
DECLARE name varchar(20);
DECLARE salary varchar(20);
DECLARE c1 CURSOR FOR SELECT AGENT_NAME , WORKING_AREA
FROM agents;
DECLARE CONTINUE handler FOR NOT FOUND SET break = 1;
OPEN c1;
my_loop:LOOP
FETCH c1 INTO name,salary;
IF break = 1 THEN
LEAVE my_loop;
END IF;
SELECT concat_ws(“ ”,name,salary);
END LOOP my_loop;
CLOSE c1;
END $$

 In MySQL view are not only query able but
updatable which means that we can use insert,
update, or delete statement on views to change
the underlying table
 Only simple views can be updated so it means
that view containing joins, group by, having,
order by, aggregate functions are not updatable.
 “WITH CHECK OPTION” is used to maintain
consistency of views so this clauses prevents us
from updating or inserting rows which are not
visible through the view.
31

 To Create view
CREATE VIEW <view_name> AS <select_statement>;
OR For with check option for insert or update
use
CREATE VIEW <view_name> AS
<select_with_where> WITH CHECK OPTION;
 TO delete view
DROP VIEW <view_name>;
32

 A Trigger is a set of SQL statements stored in the database
catalog which executed or fired when the some event
associated with table.
 Trigger are special kind of stored procedure that are
executed automatically on firing of some event
 We use “FOR EACH ROW” if there is bulk row operation in
Triggers
 Triggers are created using
CREATE TRIGGER <trigger_name> <trigger_time> <trigger_event>
ON <table_name>
BEGIN
[process]
END
 To delete Trigger
DROP TRIGGER <name>;
33

 Trigger times are : AFTER & BEFORE
 Trigger event are :
1. DELETE => OLD.key
2. INSERT => NEW.key
3. UPDATE => OLD.key & NEW.key
 Example:
34
delimiter $hiva
CREATE OR REPLACE TRIGGER wit_logs_update AFTER UPDATE ON wit
FOR EACH ROW
BEGIN
INSERT INTO logs VALUES (concat_ws("","updated data is
name:",NEW.name,"from",OLD.name,"marks:",NEW.mark," FROM ",OLD.mark,sysdate()));
END $hiva

SQL Sort Notes

More Related Content

What's hot

Similar to SQL Sort Notes

Recently uploaded

SQL Sort Notes