DBMS Midterms PDF

CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Ma...

CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Week 7 – Handout 1 Declarative Knowledge: Introduction to Database Distinct Where Clause Arithmetic Operators Comparison Operators Functional Knowledge: Explain the Data Query Language. Create database and use the syntax of Data Query Language. Intended Learning Outcomes (ILO): Apply and use MySQL syntax of Data Query Language INTRODUCTION Last topic, we discussed the Select Statements as part of Data Manipulation Language (DML). Though it is part of DML, it can only access data and cannot manipulate it, therefore it is called Data Query Language (DQL), a limited form of DML. Select Statements, as stated, can retrieve data from the database. If there are a lot of similar value and need to fetch the unique value only, the Select Distinct should be use. On this topic, Select Distinct will be introduced. Select Distinct Statement Select Distinct used to retrieve values of all rows that are different from the specified column. This will ignore duplicate values on the column and only list the different. Syntax for Select Distinct SELECT DISTINCT column1, column2,... FROM table_name; Sample: SELECT DISTINCT sectionId FROM tblStudentSection; In this command, it will show all the different value of the column sectionId and will not list the duplicate values. Page | 1 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba If you want to count the number of distinct values, run this command: SELECT COUNT (DISTINCT sectionId) FROM tblStudentSection; Where Clause. The SQL Where Clause is used to filter the rows that will be displayed. Using this clause, it will obtain and display all the rows that satisfied the stated condition. Syntax with WHERE Clause SELECT column1, column2,... FROM table_name WHERE condition; Sample: SELECT description FROM tblItems WHERE itemName = ‘palmolive’; In this sample, all values of column description with valued “palmolive” of column itemName will only displayed. Notice that palmolive has single quote in between since the value is text. But when the value is numeric, it is not needed to include in between single quotes. Comparison Operators From the above examples, where clause use equals =, a mathematical symbol to describe the condition. In MySQL, it is one of the Comparison Operators. The following are the list of Comparison Operators that can be used in condition. Page | 2 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Operator Description = Equal > Greater than < Less than >= Greater than or equal 9; In this command, it will display all rows in tblSales with itmName and price as the table column that has price greater than or equal on 50 AND quantity should be also greater than 9. Logical OR Operator The OR operator will compare two conditions and display records of a table if at least ONE condition separated by OR were satisfied or TRUE. The OR operator can be coded in MySQL either in OR word or || symbols (two pipe symbols without space). Syntax: SELECT column1, column2,... FROM tableName WHERE condition1 OR condition2 OR condition3...; Sample: SELECT itmName, price FROM tblSales WHERE price >= 50 OR quantity > 9; In this command, it will display all rows in tblSales with itmName and price as the table column that has price greater than or equal on 50. If the price is NOT greater than nor NOT Page | 6 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba equal on 50 BUT quantity is greater than 9, then the condition is still satisfied, and it will show the rows where the condition satisfied. Logical NOT Operator If we want to display rows using a condition that returns NOT TRUE, we may use NOT Operator. This operator reverse its Boolean value from the satisfied condition. If the condition is TRUE, it will reverse into FALSE and if the condition is FALSE, it will be reverse into TRUE instead. The NOT operator can be coded in MySQL in NOT word AFTER the WHERE word in MySQL command and BEFORE the condition. You can also use on NOT Operator the ! symbols (exclamation point) BEFORE the = symbol which will be written as !=. Syntax: SELECT column1, column2,... FROM tableName WHERE NOT condition1; Or it can be written as: SELECT column1, column2,... FROM tableName WHERE columnName != column_value; Sample: SELECT quantity, price FROM tblSales WHERE NOT itmName = ‘soap’; Or it can be written as: SELECT quantity, price FROM tblSales WHERE itmName != ‘soap’; In this command, it will display all rows in tblSales with quantity and price as the table column that has itmName value that is NOT EQUAL to ‘soap’. Page | 7 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Combining AND, OR and NOT The following command is the combine AND and OR Operator Sample: SELECT itmName, price FROM tblSales WHERE quantity > 9 AND (itmName = ‘soap’ OR itmName = ‘shampoo’); In this command, it will display all rows in tblSales with itmName and price as the table column that has quantity greater than 9 AND the itmName value should be EITHER in ‘soap’ or ‘shampoo’. The following command is the combine AND and NOT Operator Sample: SELECT itmName, price FROM tblSales WHERE quantity > 9 AND NOT itmName = ‘soap’; In this command, it will display all rows in tblSales with itmName and price as the table column that has quantity greater than 9 AND the itmName value should NOT be ‘soap’. IN and BETWEEN Operators IN Operator IN operator is used to search multiple specific values that will match in the set given in WHERE clause. It is also the shorthand for multiple conditions of OR. Syntax: SELECT column_name(s) FROM tableName WHERE columnName IN (value1, value2,...); Page | 8 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample: SELECT quantity, price FROM tblSales WHERE itmName IN (‘soap’, ‘shampoo’, ‘can goods’); In this command, it will display all rows in tblSales with quantity and price as the table column that has itmName value of either ‘soap’, ‘shampoo’, or ‘can goods’. BETWEEN Operator The BETWEEN Operator is used to take values on a given range. Range values can be in number, dates or text. It is written in inside the CONDITION with word BETWEEN followed by the stating value, followed by AND word and then followed by ending value. Syntax: SELECT column_name(s) FROM tableName WHERE columnName BETWEEN value1 AND value2; Sample: SELECT itmName, quantity FROM tblSales WHERE price BETWEEN 100 AND 200; In this command, it will display all rows in tblSales with itmName and quantity as the table column that has price value FROM 100 up to 200. INTRODUCTION TO AGGREGATE FUNCTIONS In MySQL, there are many functions that are pre-defined that can produce wanted results on data rows. Some can be used only numeric values while some can be use in text that includes numbers. MySQL provides 2 mainly types of functions, Aggregate and Scalar Functions. In this topic, we will explain the Aggregate Functions and its examples. Page | 9 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Aggregate Functions This functions can be used in a group of rows to give result of single value. Some examples are MIN(), MAX(), COUNT(), SUM() and AVG(). MySQL MIN() and MAX() Function Use the MIN() function if you want to get the lowest row value of the chosen column while use the MAX() function if you want to get the highest row value of the chosen column. MIN() Syntax Syntax: SELECT MIN(columnName) FROM tableName WHERE condition; Sample: SELECT MIN(Price) AS LowestPrice FROM tblItems; In this command, it will display the lowest price value from tblItems. Notice that the syntax AS LowestPrice were added to display the column name as LowestPrice instead of MIN(Price). MAX() Syntax Syntax: SELECT MAX(columnName) FROM tableName; Sample: SELECT MAX(Price) AS HighestPrice FROM tblItems; In this command, it will display the lowest price value from tblItems. Page | 10 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba COUNT(), SUM() & AVG() Use the COUNT() function if you want to get the number of rows of selected column. Syntax: SELECT COUNT(columnName) FROM tableName; Sample: SELECT COUNT(items) AS itemCount FROM tblItems; In this command, it will display the items row number from tblItems. Use the SUM() function if you want to get the summation of all rows of selected numeric column. Syntax: SELECT SUM(columnName) FROM tableName; Sample: SELECT SUM(price) AS totalAmount FROM tblSales WHERE membershipStatus = ‘Regular’; In this command, it will display the summation of all rows of column price with Regular as membershipStatus ONLY and the column will be named as totalAmount. Use the AVG() function if you want to get the average of all rows of selected numeric column. Page | 11 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Syntax: SELECT AVG(columnName) FROM tableName; Sample: SELECT SUM(price) FROM tblItems WHERE itemCategory = ‘Diaper’ and description = ‘Medium’; In this command, it will display the average of all rows of column price with Diaper as itemCategory and Medium as description ONLY. References: w3schools, “MySQL AND, OR and NOT Operators”, [Online]. Available: https://www.w3schools.com/mysql/mysql_and_or.asp. [Accessed: 31-January-2023]. dotnettutorials, “IN and BETWEEN Operator in MySQL”, [Online]. Available: https://dotnettutorials.net/lesson/in-operator-mysql/. [Accessed: 31-January-2023]. w3schools, “MySQL MIN() and MAX() Functions”, [Online]. Available: https://www.w3schools.com/mysql/mysql_min_max.asp. [Accessed: 1-February-2023]. dotnettutorials, “COUNT Function in MySQL”, [Online]. Available: https://dotnettutorials.net/lesson/count-function-in-mysql/. [Accessed: 1-February-2023] Page | 12 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Week 10-11 – Handout 1 Declarative Knowledge: Getting Ready to MySQL Limit Clause Like Operator Aliases Order by Functional Knowledge: Explain the Limit Clause, Like Operator, Aliases and Order by Clause. Create database and use the syntax of Limit Clause, Like Operator, Aliases and Order by Clause. Intended Learning Outcomes (ILO): Apply and use MySQL syntax of Limit Clause, Like Operator, Aliases, and Order by Clause. INTRODUCTION Last topic, we discussed the pre-defined functions that can produce wanted result on data rows. What if we want to limit and filter the results that produces on our displayed rows? On this topic, we will discuss the Limit Clause as well as Like Operator that both can limit and filter data row results. MySQL Limit Clause and Like Operator Limit Clause The Limit Clause is used to specify the number of retrieved data rows. It is useful on large tables with more than thousands of rows as retrieving many data will generate poor performance specially when it is not needed. LIMIT Syntax Syntax: SELECT columnName(s) FROM tableName WHERE condition LIMIT number; Page | 13 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample: SELECT * FROM tblItems LIMIT 5; In this command, it will display ONLY 5 rows from tblItems. Notice that the rows that were displayed were the 1st 5 rows that were added in the table. Adding a WHERE Clause Sample: SELECT * FROM tblSales WHERE membershipStatus = 'Regular' LIMIT 5; In this command, it will display ONLY 5 rows from tblItems with a value of Regular ONLY in column membershipStatus. LIKE Operator The LIKE Operator is used in a WHERE clause to retrieved specific string pattern. To produce string pattern, wildcard characters frequently used to signify omitted characters. % (percent) symbol signify zero to multiple characters in the result _ (underscore) symbol signify one or single character in the result The % sign and _ can be combine as wildcard characters to produce wanted string pattern. Syntax: SELECT column1, column2,... FROM tableName WHERE columnName Like pattern; Page | 14 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Sample: SELECT customerName FROM tblSales WHERE customerName LIKE 'm%'; In this command, it will display all the customer names in tblSales that starts in letter m. Here are the other samples of LIKE operators using % and _ wildcards: Syntax Expected Result LIKE '%m' Return string that ends in "m" LIKE '%sa%' Return string that have "sa" in any position LIKE '_o%' Return string that have "o" in second position LIKE '_ate%' Return string that starts in any character followed by "ate" string LIKE 'o_%' Return string that starts in "o" and character length should at least in 2 LIKE 'o__%' Return string that starts in "o" and character length should at least in 3 LIKE 'm%a' Return string that start in "m" and ends in "a" MySQL Aliases Aliases is used to give a table or column a temporary name. It only exists in the query duration. It is frequently used to make the name of the table or column more readable. Aliases in tables is useful when there are more than one table used in a query. Aliases in column is useful when functions in column were used or when two or more columns combined in a query. Syntax: SELECT columnName AS aliasName FROM tableName; Sample: SELECT SUM(price) AS totalAmount FROM tblSales; Page | 15 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba In this command, it will display the summation of all rows of column price and the column will be named as totalAmount. The alias is useful since there is a function used. Sample: SELECT (price * discount) AS ‘Discounted Amount’ FROM tblSales; In this command, it will display the Discounted Amount of all rows in table tblSales since the column price and discount (assuming this is the discount percentage in decimal format) were multiplied. The alias is useful since there are multiple columns used. Sample: SELECT a.custName, b.itemName, b.price FROM tblCustomers AS a INNER JOIN tblSales AS b WHERE a.CustId = b.CustId; In this command, the alias is useful since there are multiple tables were used in query. See the sample WITHOUT the use of ALIAS. Sample: SELECT tblCustomers.custName, tblSales.itemName, tblSales.price FROM tblCustomers INNER JOIN tblSales WHERE tblCustomers.CustId = tblSales.CustId; In this command, since there are no ALIASES were used in the table, the name of the table was used for each column selected as it is needed to add for correct syntax. It is also noticed that there is coding challenge as the name of the table were too long to be added multiple times. Page | 16 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba Order By Clause This Clause is used if you want to sort the data rows result based on specified columns. It can be in ascending order or descending. It can be applied to column on any data type such varchar, integer, double, date, etc. Syntax: SELECT column1, column2,... FROM tableName ORDER BY columnName ASC|DESC;; Sample: SELECT itemName, description FROM tblItems ORDER BY itemName; In this command, it will display all rows showing columns of itemName and description from tblItems and it will be in alphabetical order of itemName since the data type is varchar. If the query doesn’t have syntax DESC, the default order will be in ascending. Otherwise, add the syntax DESC after the column name stated in the ORDER BY. ORDER BY Multiple Columns Syntax: SELECT column1, column2,... FROM tableName ORDER BY columnName1, columnName2,... ASC|DESC;; Sample: SELECT * FROM tblItems ORDER BY description ASC, itemName DESC; Page | 17 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph  CITY COLLEGE OF Program: Bachelor of Science in Information Technology CALAMBA Outcomes – Based Teaching and Learning Plan in Database Management System (CS201) Dalubhasaan ng Lungsod ng Calamba In this command, it will display all rows showing all columns from tblItems and it will be in alphabetical ascending order of description but if there are rows that have the same description, it will be ordered in descending by itemName. References: w3schools, “MySQL LIKE Operator”, [Online]. Available: https://www.w3schools.com/mysql/mysql_like.asp. [Accessed: 2-February-2023]. dotnettutorials, “LIMIT Clause in MySQL”, [Online]. Available: https://dotnettutorials.net/lesson/limit-clause-in-mysql/. [Accessed: 2-February-2023]. dotnettutorials, “Order by Clause in MySQL”, [Online]. Available: https://dotnettutorials.net/lesson/order-by-clause-in-mysql/. [Accessed: 3-February-2023] Page | 18 Old Municipal Site, Brgy. VII, Poblacion, Calamba City, Laguna (049) 559-8900 to 8907 / (02) 8-5395-170 to 171 ccc.edu.ph