Unit Il: Database Management System ‘Visit to website: htps: /onw [Link] Chapter 11 - Structured Query Language - SQL [Link]. iene 1 Introduction 2 Advantages of using SQL 3 Types of SQL Statements 4 Data Definition Language (DDL) Statements 5 Data Manipulation Language (DML) Statements 6 Data Type of Attribute 7 SQL commands 8 CREATE Database 9 Opening a database 10 Getting listings of database and tables 11 CREATE Table 12 DESCRIBE Table 13 ALTER Table 14 DROP Statement 15 INSERTION of Records 16 The SELECT Command 17 Operators in SQL 18 Where Clause 19 Example: Use of Arithmetic Operator By: Amjad Khan Page 1of36Uni atabase Management System Visit to website: hitps://wwww learnpythondck Chapter 11 - Structured Query Language - SQL 20 Example: Use of Relational Operator 21 Example: Use of Logical Operator 22 Use of DISTINCT Keyword 23 Use of Between And Operator 24 Use of IN Operator 25 Use of LIKE Operator 26 Handling NULL Values 27 ORDER BY Clause 28 Update Command 29 DELETE Command 30 Aggregate functions 31 [Link]() 32 [Link]() 33 [Link]() 34 [Link]() 35 [Link]() 36 GROUP BY CLAUSE 37 HAVING CLAUSE 38 SQL JOINS By: Amjad Khan Page 20f36Uniti: Database Management System Visit to website: [Link] learnpythond Chapter 11 - Structured Query Language - SQL > Structured Query Language (SQL) is a standard language used for accessing databases. > The Structured Query Language (SQL) is the most popular query language used by major relational database management systems such as MySql, ORACLE, SQL Server, etc. > SQL is easy to learn as the statements comprise of descriptive English words and are not case sensitive. SQL provides statements for a variety of tasks, including: > Querying data > Inserting, updating, and deleting rows ina table > Creating, replacing, altering, and dropping objects (tables) > Controlling access to the database and its objects (tables) > Guaranteeing database consistency and integrity > SQL unifies all of the proceeding tasks in one consistent language. Advantages of using SQL: i) SQLis portable: SQL is running in all servers, mainframes, PCs, laptops, and even mobile phones. ii) High speed: SQL queries can be used to retrieve large amounts of records from a database quickly and efficiently. By: Amjad Khan Page 30f36Unit Il: Database Management System Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL iii) Easy to learn and understand: SQL generally consists of English statements and as such, it is very easy to learn and understand. Besides, it does not require much coding unlike in programming languages. iv) SQLis used with any DBMS system with any vendor: SQL is used by all the vendors who develop DBMS. It is also used to create databases, manage security for a database, etc. It can also be used for updating, retrieving and sharing data with users. v) SQLis used for relational databases: SQL is widely used for relational databases. vi) SQL acts as both programming language and interactive language: SQL can do both the jobs of being a programming language as well as an interactive language at the same time. vii) Client/Server language: SQL is used for linking front end computers and back end databases. It provides client server architecture (Email, and the World Wide Web - all apply the client-server architecture). Supports object based programming: SQL supports the latest object based programming and is highly flexible. By: Amjad Khan Page 4 of 36Unit Il: Database Management System Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL The SQL statements are categorized into different categories based upon the purpose. They are- i) Data Definition Language (DDL) statement ii) Data Manipulation Language (DML) statement iii) Transaction Control Statement iv) Session Control Statement v) System Control Statement vi) Embedded SQL Statement Out of these six, we will be studying only the first two types in this course. Data Definition Language (DDL) Statements Data Definition Language (DDL) or Data Description Language (DDL) is a standard for commands that defines the different structures in a database. DDL statements are used to create structure of a table, modify the existing structure of the table and remove the existing table. Some of the DDL statements are CREATE TABLE, ALTER TABLE and DROP TABLE. Data Manipulation Language (DML) Statements Data Manipulation Language (DML) statements are used to access and manipulate data in existing tables. The manipulation includes inserting data into tables, deleting data from the tables, retrieving data and modifying the existing data. The common DML statements are SELECT, UPDATE, DELETE and INSERT. By: Amjad Khan Page Sof 36Uni Database Management System Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL Data type indicates the type of data value that an attribute can have. Commonly used data types in MySql are: Data type Description Specifies character type data of length n where n could be any CHARI) | value from 0 to 255. CHAR is of fixed length, means, declaring Specifies character type data of length ‘n’ where n could be any value from 0 to 65535. But unlike CHAR, VARCHAR is a variable- length data type. That is, declaring VARCHAR (30) means a VARCHAR(n) |™2Ximum of 30 characters can be stored but the actual allocated bytes will depend on the length of entered string. So ‘city’ in VARCHAR (30) will occupy the space needed to store 4 characters only. INT specifies an integer value. Each INT value occupies 4 bytes of storage. The range of values allowed in integer type is - INT 2147483648 to 2147483647. For values larger than that, we have to use BIGINT, which occupies 8 bytes. Holds numbers with decimal points. Each FLOAT value occupies FLOAT 4 bytes. The DATE type is used for dates in 'YYYY-MIM-DD' format. YYYY pate _| is the 4 digit year, MM is the 2 digit month and DD is the 2 digit date. The supported range is '1000-01-01' to '9999-12-31". By: Amjad Khan 6 0f36Database Management System Visit to website: Chapter 11 - Structured Query Language - SQL To create a database, we use the CREATE DATABASE statement as shown in the following syntax: CREATE DATABASE databasename; Example: To create a database called Record, we will type following command at mysql prompt [EC LEIS 2 00S) am teed a | query OK, 1 row affected (0.02 sec) Write the following SQL statement for using/opening the database: Imysql> USE Record; Perec Eerie Gtr] 1) Show Databases command that lists names of all the databases. mysql> SHOW DATABASES; 2) Show tables command that lists names of all the tables within a database. Se ESO Tmo By: Amjad Khan Page 7 of 36ment Systern Visit to website: https: /wwww [Link] QL CREATE Table * Create table command is used to create a table in SQL. It is a DDL Chapter 11 tured Query Language type of command. * Each table must have at least one column. Syntax: CREATE TABLE tablename( attributenamel datatype constraint, attributename2 datatype constraint, attributenameN datatype constraint); Example: Create the following table: STUDENT Column Name | Data Type Size SAdno Numeric 6 SName Varchar 25 SClass Numeric 2 Sec Char 2 SFees Double 10,2 CX eae UCB ao Semel ome me > SName VARCHAR(25), > DOB DATE, SClass NUMERIC(2), come LO2 Ie ar 27- 01 GD DH Query OK, 0 rows affected (0.91 sec) By: Amjad Khan Page 8 of 36Unit Il: Database Management System Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL DESCRIBE Table We can view the structure of an already created table using the describe statement. Syntax: DESCRIBE tablename; MySQL also supports the short form DESC of DESCRIBE to get description of table. To retrieve details about the structure of relation STUDENT, we can write DESC or DESCRIBE followed by table name: mysql> DESC STUDENT; OUTPUT: Prine aa a Ivarchar(25) | YES | | char(2) | Yes Idoub1e(10,2)| Yes + 6 rows in set (0.06 sec) 90136 By: Amjad KhanUnit Il: Database Management System Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL ALTER Table It is quite possible that after creating a table, as you start using it, you may discover you've forgot to mention any column or constraint or specified a wrong name for the column. In such situation you can use the ALTER TABLE statement to alter or change an existing table by adding, changing, or deleting a column in the table. 1. Add New Column: The basic syntax for adding a new column to an existing table: ALTER TABLE table_name ADD column_name data_type (size); The following statement adds a new column mobile to the student table. (AS Dee ML M1 RD eC ELC SACU sk @ Pa query OK, 0 rows affected (0.47 sec) Pecca Ree RD TT are eC Conn ee) mysql> DESC STUDENT; OUTPUT: By: Amjad Khan to of 36Unit Il: Database Management System Visit to website: https: /wwu [Link] Key | default | Extra | + ec A ann | sclass Idoub1e(10,2)| Yes + s ad | numeric(12) | YES 4. 7 rows in set (0.06 sec) 2. Modify datatype of an attribute We can modify data types of the existing attributes of a table using the following ALTER statement, Syntax: ALTER TABLE table_name MODIFY attribute DATATYPE; Example: Suppose we need to change the size of attribute SName from VARCHAR(25) to VARCHAR(20) of the STUDENT table. The MySQL statement will be: De Sr eee ee aa CUCM ere.) query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 warnings: 0 aot 36 By: Amjad Khan‘Visit to website: htps: /onw [Link] Chapter 11 - Structured Query Language - SQL 3. Rename Column Name: Sometimes we want to change the name of a column. In MySQL, the SQL syntax for ALTER TABLE Rename Column is, ALTER TABLE table_name CHANGE O1d_column_name new_column_name data_type (size); Command to rename "mobile" to "mobileno" DEC Ew wee > CHANGE mobile mobileno NUMERIC(12); query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 warnings: 0 4. Remove an attribute Using ALTER, we can remove attributes from a table, as shown in the below syntax: ALTER TABLE table_name DROP attribute; To remove the attribute mobileno from the table STUDENT, we can write the following MySQL statement: DSC query OK, 0 rows affected (0.42 sec) Records: 0 Duplicates: 0 warnings: 0 aot 36 By: Amjad Khanment Systern ‘Visit to website: htps: /onw [Link] Chapter 11 - Structured Query Language - SQL DROP Statement Sometimes a table in a database or the database itself needs to be removed. We can use DROP statement to remove a database or a table permanently from the system. However, one should be very cautious while using this statement as it cannot be undone. DROP TABLE table_name; DROP DATABASE database_name; Let us try to remove a database table using the prop TABLE statement. Example: Ce Leama Similarly, you can delete a database using the prop DATABASE statement. The following command will permanently remove the Record database from the database server. Example: mysql> DROP By: Amjad Khan Page 13 of 36ment Systern ‘Visit to website: htps: /onw [Link] Chapter 11 - Structured Query Language - SQL Oo a OS INSERT INTO statement is used to insert new records in a table. INSERT INTO tablename VALUES(value 1, value 2,....); Let us insert some records in the STUDENT table: mysql> INSERT INTO student ->VALUES (1501, ’Ravi’,’2000-01-18’ ,11, ’A’,12000); query OK, 1 row affected (0.01 sec) If we want to provide values only for some of the attributes in a table then we shall specify the attribute name alongside each data value as shown in the following syntax of INSERT INTO statement. INSERT INTO tablename (columni1, column2, ...) VALUES (valuel, value2, ...); NOTE: The values must be given in the same order in which attributes are written in INSERT command. Example: Write a command to insert student name, class and fees in table student. By: Amjad Khan Page 14 0f 36ment Systern Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL mysql> INSERT INTO student (SName, SClass, SFees. ->VALUES (’Raman’, ,12,15000); query OK, 1 row affected (0.01 sec) "he SELECT Command: The SQL statement SELECT is used to retrieve data from the tables in a database and is also called query statement. SELECT coll, col2, FROM table_name WHERE condition Here, > coll, col2, ... are the column names of the table table_name from which we want to retrieve data. > The FROM clause is always written with SELECT clause as it specifies the name of the table from which data is to be retrieved > The WHERE clause is optional and is used to retrieve data that meet specified condition(s). Example: 1. Display student table information. DE Ee ae OR By: Amjad Khan Page 15 of 36ment Systern Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL > This will display all information of the particular table (student) in the database. 2. Retrieve selected columns The following query displays student’ admission numbers and class of all the students: De a eel CC ee) 3. To display name of 11th class student information. A iam mS a Se Co ee (Oe tee ee) a The following are the commonly used operators in SQL 1, Arithmetic Operators +, - * / 2. Relational Operators =, <, > <=, >=, <> 3. Logical Operators OR, AND, NOT Arithmetic Operators are used to perform simple arithmetic operations Relational Operators are used when two values are to be compared and Logical Operators are used to connect search conditions in the WHERE Clause in SQL with other operators. By: Amjad Khan Page 16 of 36ment Systern Visit to website: https: /wwww [Link] Selecting Specific ROWS - Where Clause: With the use of WHERE clause, only those rows that satisfied the given condition are displayed in the output Example: Use of Relational Operator 1. Display students' admission no. and name, who are paying below 10000 fees. ee amo Ca Ome ee Ca L109 2, Display students’ admission no. and name, who are paying above or equal to 10000 fees eam CU CRs Seen ea Le By: Amjad Khan Page 17 of 36Visit to website Eee aa ame Oca Seen el Cd ‘SAdmno SName [DOB SClass Sec | SFees Use of Arithmetic Operator Eager C OL ORCNSTT Ca The above command, on execution, shall increment the value for all the rows of the field Fees by 50 and shall display the Admno, Name and Fees for all the students, increased by 500. Seoweae kets 1. Display information of students in class 11A. Eee a ame Oma -> WHERE SClass = 11 and Sec = ‘A’; [ SAdmno SName DOB SClass Sec | SFees | Rav 2000-¢ A By: Amjad Khan eof 36ment Systern Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL 2. Display 11th and 12th class students’ information. Eee aa ame Oca 11 or sClass ‘SName DOB SClass Sec | SFees 3. Display students' information, who are not in 10th class. Eee a ame Oma Sn eel CoS Oe ‘SAdmno SName | DOB SClass Sec | SFees (Use of DISTINCT Keyword) > DISTINCT keyword is used to restrict the duplicate rows from the results of a SELECT statement. > Only one NULL value is returned in the results with DISTINCT keyword. By: Amjad Khan Page 19 of 36Unit Il: Database Management System Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL Example: To display different classes from table student. mysq1> SELECT DISTINCT SClass ma SUD Ly ‘onditions based on a Range (Use of Between And Operator, > SQL provides a BETWEEN operator that defines a range of values that the column value must fall for the condition to become true. > The range includes both lower and upper value. Example: Display students’ information, whose admission no between 1250 and 1605. A Ee Nome Oars -> WHERE SAdmno BETWEEN 1250 AND 1605; SAdmno ‘SName DOB SClass Sec | SFees By: Amjad Khan 200836Uni Database Management System Visit to website: Chapter 11 - Structured Query Language - SQL > The operator NOT BETWEEN retrieves the rows, which are not satisfying the BETWEEN condition. A Ee Nome Oars See TN OLN PR TY [ SAdmno ‘SName DOB SClass Sec | SFees > To specify a list of values, IN operator is used. > This operator select values that match any value in the given list. Example: Display students' information, who are in section A and B. CE ee eee OLD Se ta Or Un ID > The NOT IN operator finds rows that do not match in the list. DE ean OST > WHERE Sec NOT IN(‘A’,’B’ By: Amjad Khan 21 0f36ment Systern ‘Visit to website: tps /onvw learnpyi ™ ‘SAdmno SName [DOB SClass Sec ditions based on Pattern (Use of LIKE Operator, > Many a times we want to query find out names starting with ‘A’ or to find out pin codes starting with ‘11’. > This is called substring pattern matching. We cannot match such patterns using = operator as we are not looking for exact match. > SQL provides LIKE operator that can be used with WHERE clause to search for a specified pattern in a column. > Patterns are case — sensitive. > The LIKE operator makes use of the following two wild card characters: " % (percent)— used to represent zero, one, or multiple characters = _(underscore)— used to represent a single character > The keyword NOT LIKE is used to select rows that do not matching the specified pattern of characters. Example: 1. Query displays details of all those students whose name starts with ‘A’. CEs e age OT See ae By: Amjad Khan Page 22 of 36Unit Database Management System Visit to website: Chapter 11 - Structured Query Language - SQL 2. Query displays details of all those students whose name ends with 'r'. CE eee ee Me OUTDO -> WHERE SName LIKE '%r'; SAdmno SName | DOB SClass Sec | SFees 3. Query displays details of all those students whose name consists of exactly 5 letters and starts with any letter but has ‘mar’ after that. Eee aa ame Oca -> WHERE SName LIKE '__mar'; SAdmno SName [DOB SClass Sec | SFees 4. Query displays names of all the students containing 'mi' as a substring in name. Eee aa ame Oca -> WHERE SName LIKE 'Xmi%"; By: Amjad Khan Page 23 of 36Unit Il: Database Management System Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL ‘SAdmno SName [DOB SClass Sec | SFees 5. Query displays names of all students containing ‘a’ as the second character. CO eee ee De ee a Shdnno | SName [DOB SClass__ [See |SFees | Leet ereg LU BA YEN > The NULL value in a column can be searched for in a table using IS NULL in WHERE clause. > Non— NULL values can be listed using IS NOT NULL. Example: 1. Query displays details of all those Students who have not been given fees. This implies that the fees column will be blank. CA ee a OTD ee tt 2. Query displays names of all the students who have been given fees. This implies that the fees column will not be blank. By: Amjad Khan 24036Database Mans ment System ‘Vist to website: https:/ wu [Link] Chapter 11 - Structured Query Language - SQL Aas en ie ee tt ORDER BY Clause: > ORDER BY clause is used to display the result of a query ina specific order (sorted order). > The sorting can be done in ascending or in descending order. > It should be kept in mind that the actual data in the database is not sorted but only the results of the query are displayed in sorted order. > Descending order is specified by DESC. > Ascending order is specified by ASC. > Ordering can be performed on multiple attributes, separated by commas. Example: 1. Query to displays details of all the students in ascending order of their Name. CNET Eanes ORS Semel aac By: Amjad Khan Page 25 of 36ment Systern ‘SAdmno SName [DOB SClass Sec | SFees é mit A 2. Query displays details of all the employees in descending order of their date of birth. CO eee ee De eae) 11) no ter ‘SAdmno DOB SClass LOE TK EDC The UPDATE statement is used to update existing data in a table. UPDATE table_name SET columni_name = value1, column2_name = value2,... WHERE condition; By: Amjad Khan Page 26 of 36‘Visit to website: htps: /onw [Link] Chapter 11 - Structured Query Language - SQL Here, > column1_name, column2_name,... are the names of the columns or fields of a database table whose values you want to update. > You can also combine multiple conditions using the AND or OR operators, Example: 1. Increase fees value by 500. DAE oe -> SET SFees = SFees + 500 Example: Updating a Single Column The following SQL statement will update the SName field of the STUDENT table and set a new value where the student admission number i.e. SAdmno is equal to 1501. By: Amjad Khan 27036Unit Il: Database Management System Visit to website: Chapter 11 - Structured Query Language - SQL SAdmno SName DOB SClass Sec | SFees Example: Updating Multiple Columns: The following example will update the SFees and Sec field of an existing student in the student table whose SAdmno is 1605. eT TELESIS > SET SFees = 10500, Sec pee Check the output Eee Name Os By: Amjad Khan Page 28 of 36Unit Il: Database Management System Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL ‘SAdmno SName | DOB SClass Sec | SFees 50 Qamar 8 2500.00 1001 Umar it 11500.00 1201 Annu 10 11500.00 1605 amit iL 10500.00 1501 Sara it 12500.00 1520 Sunita 12 12500.00 1610 Sumit 10 11500.00 The DELETE statement is used to delete one or more record(s) from a table. DELETE FROM table_name WHERE condition; Warning: The WHERE clause in the DELETE statement specifies which record or records should be deleted. It is however optional, but if you omit or forget the WHERE clause, all the records will be deleted permanently from the table. Example: Suppose the student with admission number 1501 has left the school. We can use the following MySQL statement to delete that record from the STUDENT table. Amjad Khan 29 0f 36Database Management System Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL Reha Ce Sees Query ok, 1 row affected (0.06 sec) CAC Eanes ORS ‘SAdmno SName | DOB ‘SClass Sec | SFees Aggregate functions are used to implement calculation based upon a particular column. These functions always return a single value. Aggregate functions are: 1. SUM() 2. AVG() 3. MAX() 4, MIN() 5. COUNT() AND COUNT(*) By: Amjad Khan Page 30 of 36Uni Database Management System Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL [Link](): This function is used to find the total value of a particular column. Example: eT eee Cte) Dee eRe SUM (SFees) [Link](): This function is used to find the average value of a particular column. Example: Ber ecae te) aD aL AVG (SFees) [Link](): This function is used to find the maximum value of a particular column. Example: A eam aCe) Pee OSI td By: Amjad Khan s10f36ment Systern Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL MAX (SFees) [Link](): This function is used to find the minimum value of a particular column. Example: Became) Dee aI MIN (SFees) [Link](): This function is used to find the number of values (i.e. number of rows) of 3 particular column The COUNT (Column_Name) function returns the number of values (NULL values will not be counted) of the specified column. Example: To count the classes from the student table mysql> SELECT COUNT (SClass) Sea aI ad By: Amjad Khan Page 32 of 36ment Systern COUNT (SClass) The COUNT (*) function returns the total number of records in a table, counts NULL values also Example: To count the number of records from the table Employee. CO Ee nemo LG) Sea aI ad COUNT (*) The COUNT (DISTINCT column_name) function returns the number of distinct values of the specified column. Example: To count the different sections from the table STUDENT. mysq1> SELECT COUNT (DISTINCT Sec) See OMA ad COUNT (DISTINCT Sec) By: Amjad Khan Page 33 of 36ment Systern * The GROUP BY clause groups records into summary rows. © GROUP BY returns one records for each group. AVG, etc. * GROUP BY can group by one or more columns. SELECT column-names FROM table-name WHERE condition GROUP BY column-names Example: 1) Display number of students in each class. mysqI> SELECT COUNT (*), SClass SOUL Sanco a) ae COOH Count (*) SClass By: Amjad Khan iP BY CLAUSE * GROUP BY typically also involves aggregates: COUNT, MAX, SUM, Page 34 of 36ment Systern ‘Visit to website: htps: /onw [Link] Chapter 11 - Structured Query Language - SQL 2) Display sum of fees for each class. Ce ea CSCS) ee OS Bek CLE aE SClass SUM _(SFees) HAVING CLAUS! * HAVING filters records that work on summarized GROUP BY results. * HAVING applies to summarized group records, whereas WHERE applies to individual records. © Only the groups that meet the HAVING criteria will be returned. * HAVING requires that a GROUP BY clause is present WHERE and HAVING can be in the same query. SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition ORDER BY column-names By: Amjad Khan Page 35 of 36ment Systern Visit to website: https: /wwww [Link] Chapter 11 - Structured Query Language - SQL Example: Display sum of fees which is more than 8000 for each class Ce ea CSCS) ee OS > GROUP BY SClass SNP ease SUM (SFees) The process/function of combining data from multiple tables is called a JOIN. SQL can extract data from two or even more than two related tables by performing either a physical or virtual join on the tables using WHERE clause. The types of SQL joins are as follows: 1. Cartesian Product (Cross Product) 2. Equi Join 3. Self Join 4. Non-Equi Join 5. Natural Join By: Amjad Khan Page 36 of 36