SQL Structured Query Language PDF

SQL STRUCTURED QUERY LANGUAGE CCC 220 – Information Management Mr. Joseph S. Joaquin  Structure Query Language 2 Objectives At the end of this topic, students will be able to: Determine the purpose of learning SQL in managing databases; Identify the diff...

SQL STRUCTURED QUERY LANGUAGE CCC 220 – Information Management Mr. Joseph S. Joaquin  Structure Query Language 2 Objectives At the end of this topic, students will be able to: Determine the purpose of learning SQL in managing databases; Identify the different capabilities of SQL in managing databases (including relational databases); Determine the correct syntax of SQL in manipulating records in databases; Create SQL statements to manipulate records in MariaDB database; and Create a Window-based and Web-based application using the correct SQL statements in manipulating records in databases.  Structure Query Language 3 What is SQL? SQL is a standard language for accessing and manipulating databases.  Structure Query Language 4 What can SQL do? SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views  Structure Query Language 5 SQL is a Standard – But… Although SQL is an ANSI (American National Standards Institute) standard, there are different versions of the SQL language. However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.  Structure Query Language 6 SQL Statements Most of the actions you need to perform on a database are done with SQL statements. Note: SQL is NOT case sensitive: select is the same as SELECT.  Structure Query Language 7 Semicolon after SQL Statements Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.  Structure Query Language 8 Some of The Most Important SQL Commands SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index  Structure Query Language 9 Select Statement The SELECT statement is used to select data from a database table. The result is stored in a result table, called the result-set. Syntax SELECT column_name FROM table_name; or SELECT * FROM table_name; …where * means, all columns from table_name…  Structure Query Language 10 Select Distinct Statement In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values. The DISTINCT keyword can be used to return only distinct (different) values. Syntax SELECT DISTINCT column_names FROM table_name;  Structure Query Language 11 WHERE Clause The WHERE clause is used to filter records or to extract only those records that fulfill a specified criterion. Syntax SELECT column_name, column_name FROM table_name WHERE column_name operator value;  Structure Query Language 12 WHERE Clause (Continued…) Operators Supported by WHERE Clause  Structure Query Language 13 AND & OR Operators The AND operator displays a record if both the first condition AND the second condition are true. The OR operator displays a record if either the first condition OR the second condition is true. Example SELECT * FROM Customers WHERE Country='Germany‘ AND City='Berlin'; SELECT * FROM Customers WHERE City='Berlin‘ OR City='München';  Structure Query Language 14 AND & OR Operators (Continued…) Combining AND & OR You can also combine AND and OR (use parenthesis to form complex expressions). Example SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');  Structure Query Language 15 ORDER BY Keyword The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. Syntax SELECT column_name, column_name FROM table_name ORDER BY column_name, column_name ASC|DESC;  Structure Query Language 16 ORDER BY Keyword (Continued…) Example Ascending SELECT * FROM Customers ORDER BY Country; or SELECT * FROM Customers ORDER BY Country ASC; Descending SELECT * FROM Customers ORDER BY Country DESC;  Structure Query Language 17 ORDER BY Keyword (Continued…) Order by several columns example: SELECT * FROM Customers ORDER BY Country, CustomerName;  Structure Query Language 18 Insert Into Statement The INSERT INTO statement is used to insert new records in a table. It is possible to write the INSERT INTO statement in two forms. The first form does not specify the column names where the data will be inserted, only their values: INSERT INTO table_name VALUES (value1,value2,value3,...);  Structure Query Language 19 Insert Into Statement (Continued…) The second form specifies both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...); Example INSERT INTO Customers (CustomerName, ContactName) VALUES ('Cardinal', 'Tom B. Erichsen');  Structure Query Language 20 Update Statement The UPDATE statement is used to update existing records in a table. Syntax UPDATE table_name SET column1=value1, column2=value2,... WHERE some_column=some_value;  Structure Query Language 21 Update Statement (Continued…) Example UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds Futterkiste';  Structure Query Language 22 Delete Statement The DELETE statement is used to delete rows in a table. Syntax DELETE FROM table_name WHERE some_column=some_value; Example DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';  Structure Query Language 23 SQL Injection SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input. Injected SQL commands can alter SQL statement and compromise the security of a web application.  Structure Query Language 24 SELECT TOP Statement The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance. Note: Not all database systems support the SELECT TOP clause.  Structure Query Language 25 SELECT TOP Statement (Continued…) Syntax SELECT TOP number/percent column_name(s) FROM table_name; Equivalent to MySQL Syntax SELECT column_name(s) FROM table_name LIMIT number;  Structure Query Language 26 SELECT TOP Statement (Continued…) Example for MySQL Database SELECT * FROM tbStudent LIMIT 5;  Structure Query Language 27 LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Syntax SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;  Structure Query Language 28 LIKE Operator (Continued…) Example SELECT * FROM Customers WHERE City LIKE 's%'; Note: The statement selects all customers with a City starting with the letter "s“. Other Example SELECT * FROM Customers WHERE Country LIKE '%land%';  Structure Query Language 29 SQL Wildcard Characters In SQL, wildcard characters are used with the SQL LIKE operator. SQL wildcards are used to search for data within a table.  Structure Query Language 30 SQL Wildcard Characters (Continued…) Example 1 SELECT * FROM Customers WHERE City LIKE 'ber%'; Note: SQL statement selects all customers with a City starting with “ber”. Example 2 SELECT * FROM Customers WHERE City LIKE '%es%';  Structure Query Language 31 SQL Wildcard Characters (Continued…) Example 3 SELECT * FROM Customers WHERE City LIKE '_erlin'; Note: SQL statement selects all customers with a City starting with any character, followed by "erlin“. Example 4 SELECT * FROM Customers WHERE City LIKE 'L_n_on';  Structure Query Language 32 SQL In Operator The IN operator allows you to specify multiple values in a WHERE clause. Syntax SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2,...); Example SELECT * FROM Customers WHERE City IN ('Paris', 'London');  Structure Query Language 33 SQL Between Operator The BETWEEN operator selects values within a range. The values can be numbers, text, or dates. Syntax SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; Example 1 SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;  Structure Query Language 34 SQL Between Operator (Continued…) NOT BETWEEN Example SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20; BETWEEN Operator with IN Example SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);  Structure Query Language 35 SQL Between Operator (Continued…) BETWEEN Operator with Text Value Example SELECT * FROM Products WHERE ProductName BETWEEN 'C' AND 'M'; BETWEEN Operator with Date Value Example SELECT * FROM Orders WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;  Structure Query Language 36 SQL Aliases SQL aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable. Example Alias Syntax for Columns SELECT column_name AS alias_name FROM table_name;  Structure Query Language 37 SQL Aliases (Continued…) SQL Alias Syntax for Tables SELECT column_name(s) FROM table_name AS alias_name; Alias Example for Table Columns SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;  Structure Query Language 38 SQL Aliases (Continued…) In the following SQL statement combines four columns (Address, City, PostalCode, and Country) and create an alias named "Address": SELECT CustomerName, Address+', '+City+', '+PostalCode+', '+Country AS Address FROM Customers; MySQL Example: SELECT CustomerName, CONCAT(Address,', ',City,', ',PostalCode,', ',Country) AS Address FROM Customers;  Structure Query Language 39 SQL Joins An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met. Example SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;  Structure Query Language 40 SQL Joins (Continued…) Different SQL JOINs INNER JOIN: Returns all rows when there is at least one match in BOTH tables LEFT JOIN: Return all rows from the left table, and the matched rows from the right table RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table FULL JOIN: Return all rows when there is a match in ONE of the tables  Structure Query Language 41 INNER JOIN Keyword The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. Syntax SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;  Structure Query Language 42 INNER JOIN Keyword (Continued…) Example SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the "Customers" table that do not have matches in "Orders", these customers will NOT be listed.  Structure Query Language 43 LEFT JOIN Keyword The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. Syntax SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;  Structure Query Language 44 LEFT JOIN Keyword (Continued…) Example SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; Note: The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).  Structure Query Language 45 RIGHT JOIN Keyword The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. Syntax SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;  Structure Query Language 46 RIGHT JOIN Keyword (Continued…) Example SELECT Orders.OrderID, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID ORDER BY Orders.OrderID; Note: The RIGHT JOIN keyword returns all the rows from the right table (Employees), even if there are no matches in the left table (Orders).  Structure Query Language 47 FULL OUTER JOIN Keyword The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins. Syntax SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;  Structure Query Language 48 UNION Keyword The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order. Syntax SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;  Structure Query Language 49 UNION Keyword (Continued…) Syntax of UNION ALL SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;  Structure Query Language 50 UNION Keyword (Continued…) Example SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;  Structure Query Language 51 SQL Functions SQL has many built-in functions for performing calculations on data. SQL Aggregate Functions SQL aggregate functions return a single value, calculated from values in a column. Useful aggregate functions: AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum  Structure Query Language 52 SQL Functions (Continued…) SQL Scalar functions SQL scalar functions return a single value, based on the input value. Useful scalar functions: UCASE() - Converts a field to upper case LCASE() - Converts a field to lower case MID() - Extract characters from a text field LEN() - Returns the length of a text field ROUND() - Rounds a numeric field to the number of decimals specified NOW() - Returns the current system date and time FORMAT() - Formats how a field is to be displayed  Structure Query Language 53 AVG() Function The AVG() function returns the average value of a numeric column. Syntax SELECT AVG(column_name) FROM table_name Example SELECT AVG(Price) AS PriceAverage FROM Products;  Structure Query Language 54 Count() Function The COUNT() function returns the number of rows that matches a specified criteria. Syntax SELECT COUNT(column_name) FROM table_name; Count(*) Syntax SELECT COUNT(*) FROM table_name;  Structure Query Language 55 Count() Function (Continued…) COUNT(DISTINCT column_name) Syntax SELECT COUNT(DISTINCT column_name) FROM table_name; Example SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders WHERE CustomerID=7;  Structure Query Language 56 FIRST() Function The FIRST() function returns the first value of the selected column. Syntax SELECT FIRST(column_name) FROM table_name; MySQL Syntax SELECT column_name FROM table_name ORDER BY column_name ASC LIMIT 1;  Structure Query Language 57 LAST() Function The LAST() function returns the last value of the selected column. Syntax SELECT LAST(column_name) FROM table_name; MySQL Example SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 1;  Structure Query Language 58 MAX() Function The MAX() function returns the largest value of the selected column. Syntax SELECT MAX(column_name) FROM table_name; Example SELECT MAX(Price) AS HighestPrice FROM Products;  Structure Query Language 59 MIN() Function The MIN() function returns the smallest value of the selected column. Syntax SELECT MIN(column_name) FROM table_name; Example SELECT MIN(Price) AS SmallestOrderPrice FROM Products;  Structure Query Language 60 SUM() Function The SUM() function returns the total sum of a numeric column. Syntax SELECT SUM(column_name) FROM table_name; Example SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;  Structure Query Language 61 GROUP BY Statement The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. Syntax SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;  Structure Query Language 62 GROUP BY Statement (Continued…) Example SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID=Shippers.ShipperID GROUP BY ShipperName;  Structure Query Language 63 HAVING Clause The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. 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;  Structure Query Language 64 HAVING Clause (Continued…) Example SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;  Structure Query Language 65 UCASE() Function The UCASE() function converts the value of a field to uppercase. Syntax SELECT UCASE(column_name) FROM table_name; Example SELECT UCASE(CustomerName) AS Customer, City FROM Customers;  Structure Query Language 66 LCASE() Function The LCASE() function converts the value of a field to lowercase. Syntax SELECT LCASE(column_name) FROM table_name; Example SELECT LCASE(CustomerName) AS Customer, City FROM Customers;  Structure Query Language 67 MID() Function The MID() function is used to extract characters from a text field. Syntax SELECT MID(column_name, start, length) AS some_name FROM table_name;  Structure Query Language 68 MID() Function (Continued…) Parameter Description column_name Required. The field to extract characters from start Required. Specifies the starting position (starts at 1) length Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text Example SELECT MID(City,1,4) AS ShortCity FROM Customers;  Structure Query Language 69 LEN() Function The LEN() function returns the length of the value in a text field. Syntax SELECT LEN(column_name) FROM table_name; Example SELECT CustomerName,LEN(Address) as LengthOfAddress FROM Customers;  Structure Query Language 70 ROUND() Function The ROUND() function is used to round a numeric field to the number of decimals specified. Syntax SELECT ROUND(column_name,decimals) FROM table_name; Parameter Description column_na Required. The field to round. me decimals Required. Specifies the number of decimals to be returned.  Structure Query Language 71 ROUND() Function (Continued…) Example SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;  Structure Query Language 72 NOW() Function The NOW() function returns the current system date and time. Syntax SELECT NOW() FROM table_name; Example SELECT ProductName, Price, Now() AS PerDate FROM Products;  Structure Query Language 73 FORMAT() Function The FORMAT() function is used to format how a field is to be displayed. Syntax SELECT FORMAT(column_name,format) FROM table_name; Parameter Description column_na Required. The field to be formatted. me format Required. Specifies the format.  Structure Query Language 74 FORMAT() Function (Continued…) Example SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM- DD') AS PerDate FROM Products;  Structure Query Language 75 End