SQL Handwritten Notes

CLASSTIME Pg. No. Date SQL Introduction Database: It is an Organized collection of data so that it can be easily accessed. To manage these databases, DBMs (Database Management System ) are used. DBMS Database A Types OF DBMSs:- E Relational DBMs ATUL KUMAR (Linkedin /Twitter NOTES GALLERH CoDING BUeS(Telegram e Non-Relational DBMS Relational DBMS:- In this DBms, data stored in table format. ROLlNO Name Class Jai 5th 2 Amar 7th 3 Anuj 5 प Ram 8th For Example:- My Sol, Oracle. L CLASSTIME Pg. No. Darte/ Non- Relational DBms :- In this DBms data is stored in Key - value pair. "ROIHNO": 1, "Class":5th " " Name":jai" } (Non- Relational DBms). For Example: NongoDB, Redis SCOL It stand for Structured Query Lanquage. Seel is used for update, delete, insert data in table or Relational Database. Relational Database. RDBMS SQL 2 CLASSTIME Pg. No. Date SOL CREATE Command:- It is used for create Tables. Syntax CREATE TABLE tablename ( column 1 datatype, column 2 datatype, Scal Keywords are case- insensitive. In Myscel, Case - insensittve is an option you can turn on and Off. Por Example: CREATE TABLE User D FirstName varchar, LastName Varchar, Email-id Varchar, Password varchar. First Name LastName Email.1d Password. 3. SQL INSERT INTO command:- CLASSTIME Pg. No. Date / It is used to insert data into tables. Syntax:INSERT INTO tableName (column 1 Column 2... ) values (value 1, value2... )3 A row of database table is known as record or a tuple. A column of database table is Known as an attribure. For Example: INSERT INTO USER (FirstName LastName, Email-id, Password) values ( Jai, Sharma, abc @gmail.com,abc# 123) First Name Jai Last Name Email-Id Password Shama abc@gmail abc #123 How to Insert Multiple Record (row, tuple) :- VAWES( Jar, Sharma, abc@gmail.com,123),(Jaya,Shama, xyz@gmall.com,abc);4 CLASSTIME/Pg. No. Date SOL SELECT Command:- It is used to retrieves data from the table. Syntox:- SELECT COlumn 1, Coumn 2 From tableName; To Select Complete table, use * ( star SELECT * From tableName; 2 Example:- FirstName Last Name Password jai Kumar 123 Jaya Singh abc Amit Sharma x92 Table:-USER Command: SELECT FirstName From USER; Oupur: ATUL KUMAR (wnkedin /Trwitter) NOTES CIALLERY / CODING BuGS (TelegramFirst Name Jai Jaya Amit CLASSTIME Pg. No. Darte How Does SQL Work SQL Query Query Language Processort Passer + optimizer DBMS Engine File Manager + Transactton Manager. Physical Database Passing:r In this Process, Query statement is tokenized. Optimising It Optimize the best algorithm for the byte Code. FROM: It is used to specify the tables From which data fetched. 6 CLASSTIME Pg. No. Darde/ WHERE: It is used to filter records based on the given condition. JOIN: It is used to combine data from tables based on a common field. GROUP BY: It is used to group records based on our requlrement. HAVING:- It is used to filter groups. ORDER BY :- It is used to sort the data in ascending or descending Order. SELECT It is used to retrieves data From the table. LIMIT : It is used to specify how many rows are returned. CLASSTIME Pg. No. Date SQL Constraints constraints ATUL KUMenR (Linkedin /TWitter) NOTES GALERY / CoDING Bucs (Telegram)CHECK UNICQUE DEFAULT INDEX ↓ NOTNULL PRIMARY FOREIGN KEY KEY Tnese Constraints also Known as Integrity Constraints. SOL Constraints:- Constraints are the rwes and restrictions applied on the dara in a table. 8 CLASSTIME Pg. No. Date NOT NULL: Value cannot be Null in a column UNIQUE: Value cannot be same in a column PRIMARY KEY: Used uniquely identify a row. FOREIGN KEY : References a row in another table. e CHECK: Satisfies a specific Condition. DEFAULT : Set default value. CREATE INDEX:- Used to speedup the read process. SQL QUERY EXECUTION ORDER FROM JOIN WHERE GROUPBY HAVING SELECT ORDER BY LIMIT CLASSTIME Pg. No. Date G DIFFERENCE AND UPDATE ALTER • It is a DDL. It is used for adding, deleting, and modifying attributes of the table. • Changes are made to the table Structure. Bydefault, all the yalues in the tuple are initialized as nall if the ALTER command is used. BETWEEN ALTER ATUL KUMAR LLinkedin/ TwitHer) NOTES GALLERY /CODING BUES (Twitter) UPDATE • It is a DML. It is used for updating the data in the existing table. Changes are made to the data. • It sets the Specified value to the tuple if update command is used. ها CLASSTIME Pg. No. DBe Syntox:- CQL SELECT DISTINCT It is used to retum only unique values from a specified column in a table. SELECT DISTINCT Column_name FROM tablename; Example:- First Name Last Name Password Jai Kumar 123 Jaya Singh 123 Amit Sharma XY2 Command SELECT Distinct Password FROM USER; Outpyt: Password 123 xyz い CLASSTIME Pg. No. Darte SOL WHERE CLAUSE: It is used to filter rows in a table based on a specified condition. Syntax: SELECT Column_name FROM table_name WHERE Condition; Example:- FirstName Last Name Ase Jai kamar 19 Jaya Singh 20 Amit Sharma 21 Table :- USER Command: SELECT FirstName, LastoName FROM USER WHERE Age 220; Output:~ First Name Last Name Amit Shama. 12 CLASSTIME Pg. No. Date SOL AND The AND Operator returns true if both condition are true, and false otherwise. Syntax: INMERE Condition 1 AND Conditiona; SeL OR:- ATUL KomAR (Linkedin/Twiter) NOTESCALLERY /CODING BUGS (Telegram It returns true if either conditton is true, and false if both conditions are false. Suntay WHERE Conditiont OR conditton 2; SOL NOT:- It returns the opposite of a Condition. Suntay?WHERE NOT condition; AND, OR, NOT Operator are used to lombine Conditions in a where clause to Create More complex Fltering Conditions. 13 CLASSTIME Pg. No. Date SOL ORDER BY: It is used to sort the result of a CQuery in ascending or descending order. Syntex SELECT columnI, column 2,... FROM table-name ORDER By Column 1 (ASC] [DESC], Column 2 LASCIIDESC],.... ASC: It is used to sort the result in ascending Order. DESC: It is used to sort the result in descending order. EYample FirstName LastName Age. Jai Kumar 49 Jaya Singh 20 Amit Sharma 21 Table:- User. Command: SELECT * FROm user ORDER BY Age; CLASSTIME Pg. No. Darte / Output First Name Last Name Age Jay a Singh 20 Amit Sharma 21 Jay Kumar 49 INSERT INFO:- It is used to insert data into a table. Sentex Insert into tablename (Column1, Column2,.) Values (Value1, value 2.); Note: These must be the same number of values as the same number of columns specified. Example:- FirstName LastName Ase. Jai Kumar 10 Jaya Singh 15 Amit Sharma 20 Table !-user Command Insert into user (TirstnName, LastName, Age)values (abc, xyz, 25); รา Outpur: CLASSTIME Pg. No. Date First Name Last Name Age. Jai Kumar 10 Jaya Singh 15 Amit Sharma 20 abc xyz 25 SOL NULL Values:- It is used represent missing or unknown data. Nore: Null is different from zero or empty String. ATUL KUMAR (Linkedin /Twitter) NOTES GALLERY / CODING BUGS( Telegram) Insert NULL Value:- INSERT INTO tablename ((Column 1, Column 2...)VALUES (Value1 , Nul,..); To chek for Null Varues: IS NULL SELECT Column1, column&, From table_name WHERE column2 IS NULL TS NOT NULL:- CLASSTIME Pg. No. Dae SELECT Column1, Column 2, FROM table_name WHERE column 1 IS NOT NULL; SQL UPDATE:- It is used to modify existing data in table. Syntaxr UPDATE table_name SET Column1 = Value 1, Column2 =value 2. WMERE Some_column = some_ value; SET: It is used to specify the Column and values to update. Eyample: FirstName Last Name Age Jat Kumar 10 Jaya Singh 15 Amit Sharma 20 Table: Users Command:- CLASSTIME Pg. No. Date/ UPDATE USERS SET age = age + 1; Output: First Name Last Name Age Jai Kumar 1 Jaya Singh 16 Amit Sharma 21 Arui eemoR (Linkesin/Tusitter) SQL DELETE :- NOTES GALLERY/ CODING BuGS (Telegram It is used to remove existing record from a table in a saL Database! Syntax Note: DELETE FROm tablename INHERE condition; This Operation is not reversible, so be Careful When using DELETE statements ! 18 CLASSTIME Pg. No. Date T e SelL Wildcards:- Wildcards are special characters used in saL 'LIKE' operator to search for a specific pattern in a column of a table. The percent sign (%.) represents zero, one or multiple characters. The underscore sign (-) represents one, Single character. SOL LIKE: It is used to search for a specific pattern in a column of a table. Syntax:- SOL IN: SELECT Column 1, Column 2,... FROm tablename WHERE Column-name LIke pattern; It is used to speciry muitiple values in a WHERE clause for filtering data. 9 syntax CLASSTIME Pg. No. Date SELECT COlumn 1, Column 2--. FROm table_name WYERE COlumn name IN (value1, value 2); Sell Between:- It is used to filter data based on a range of values in a WHERE clause. Suntax Sal Alias SELECT Column1, Column 2,... FRoM table_name WHERE Column_name Between Value1 AND Value 2; It is used to give a temporary name to a table or a column in a query. Syntay SELECT column-name as alias-name from table_name; 20 CLASSTIME Pg. No. Daite SQL UNION Operator: It is used to combine the result sets of two or more SELECT statements into a single result sets. Note:- It returns only distinet rows. Syntax SELECT Column_ name FROM table name 1 UNION SELECT Column_name FROm table_name_2; Example SELECTA SELECTВ 3 2 Union 3 S २ 3 5 CLASSTIME Pg. No. Date SaL GROUP BY :- It is used to group rows that have the same values into Summary rows, ike "Find the number of customers in each city.5" SOL HAVING:- It is used to filter the results of a "GROUP BY? query Based on the values of an aggregate function. SCOL EXISTS : ATUL KUMAR Chinkedin /Twitter) NOTES GALLERY /CODING BUGs (Telegram It is used to check if a subquery retums any roWS. It returns a boolean value. SOL ALL: It is used to Compare a value with the result of a cubquery It retums true if the value is true for all elements. 22 CLASSTIME Pg. No. Date SQL ANY It isreturns also true a Comparison if the value Operatos is true fox at least one element. TYPES OF Exror In SQL Syntax Grrors: These occur when sal statements do not follow the correct syntay and structure of the language. Semantic Error:- These occur wwhen the saL statement is grammatically correct, But does not produce the desired yesult due to incorrect Logic. Constraint Voilations : These occur when the ScoL Violates one or more constraints on the database. Datatype Errors& These occurs when data is Inserted in a way that does not match the expected data type. For Example:- Insert a String into a numeric field. CLASSTIME Pg. No. Date / Transaction Error:- These occur when a transaction fails due to problems with locking. SQL JOINS SOL JOINS: These Statements allows us to access informattion from two or more tables at once. They also keep our database normalized. Types of Joins :- INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN ATUL KOMAR (Linkedin /Twittor) NOTES GALLERY /CODInNG BuGS (TelegranINNER JOIN :- It retarns dataset that have matching values in both tables. table 1 table 2 CLASSTIME Pg. No. Derte SELECT Column name FROM table 1 INNER JOIN table 2 ON table. Column_name =table 2. Column_name LEFT JOIN LEFT JOIN:- It returns all records from the Teft table and matched records from the right. table 1 table 2. SELECT column_name FROm table 1 LEFTJOIN table 2. ON table1. column_ name =table 2. Column_name. RIGHT JOIN RIGHT JOIN : It returns all records from the right table and the match records from the Left. 25 table 1 table2 CLASSTIME Pg. No. Darte SELECT Columnname FROM table 1 RIGHT JOIN table 2 ON table.Column_name=table 2. C0lumn-name; FULL JOIN FULL JOIN:- It returns all records when there is a match in either the left table or right table. table 1. ← table 2. ATUL KOMAR (Linkedin/Twitter)SELECT Column-name FROM table 1 FULL OUTER JOIN table 2 NOTES GALLERy/CODNNG BuGS(TelegraON tablet. Column_name=table2. column_name WHERE Condition 26 CLASSTIME Pg. No. Darte * * Primary Key and Foreign Key Primary Key: It is a unique value that is used to identify a rowin a table. If you are thinking about unique constant, then you must to khow the difference Unique Constant can store null value also Primary Key cannot store null vatue. Synpe Create table tablename ( Column I datatype, Column 2 datatype, Primary Key (column name) A table can contain only one Primary key. Foreign Key: It is a key wwhich is used to link two table together. Foreign Key can have mutiple null value. Synbays Create table tablename ( columnl datatype, column ? datatype, Porelgn Key I Column 1, -) References t primary Key table name] زد 27 Let's Take Example: CLASSTIME Pg. No. Date RollNlo. Name Address Atul Deini 2 Abhi Mumbai 3 Zayn Delni Primary Key. Name and address are not unique. Here Roll No is uniquе so we discussed before, primary Key is unique and not null value. Result table. Marh Science English 10% 80% 70%. 90% 60% 20% 70% 50% 21% 28. CLASSTIME Pg. No. Darte Math Science English 107 807. 70% 90% 60% 20% 70% 50%. 211 → You want to check ROll NO. 3 marks in Maths, Foreign Key come into Picture student table + Result table. we have to add Primary Key In Result table Which act as Foreign Key. ATUL KUMAR (Linkedin/Twttter) NOTES GALLERY COPING Bues( BUGS TelegramForeign Key Maths Science English Roll No. 10%. 80% 70% 90% 60% 207. २ 70-%. 30% 21% 3 29 CLASSTIME Pg. No. Date COMMIT AND ROLLBACK commit and Rollback are transaction statements. Commit:- It is used to storing changes Permanantly performed by a transaction. Commit Syntax COMMIT Rollback It is used for reterting changes Performed by a transaction. Rollback Syntax ATUL Kumme (Linkedin Toitter) NOTES CALLERY /COPING BuGs (TelegramRollback 30