際際滷

際際滷Share a Scribd company logo

Dynamic websites lec2

Belal Arfa
Belal Arfa

This document discusses various SQL concepts including the LIKE operator, TOP clause, UNION vs UNION ALL, IN operator, aggregation functions, GROUP BY clause, HAVING clause, and the difference between HAVING and WHERE. It provides examples of queries using these concepts on sample tables and explains the purpose and syntax of each concept. Quizzes with sample queries and answers are also included.

1 of 19
Downloaded 12 times
Dynamic Websites PHP with Oracle DB By Belal Arfa
Section 2 In this section will discuss 1- Examples on last section 2- Quiz 3- Like Operator 4- Top Stmt 5- Union VS Union ALL 6- IN Operator 7- Aggregation Functions 8- Group By 9- Having 10- Difference Between Having and Where
Quiz (1) Relations: Movie(title, year, length, inColor, studioName, producerC#) StarsIn(movieTitle, movieYear, starName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, netWorth) Studio(name, address, presC#) Queries: a) Find the address of MGM studios. b) Find Sandra Bullocks birthdate. c) Find all the stars that appear either in a movie made in 1980 or a movie with Love in the title. d) Find all executives worth at least $10,000,000. e) Find all the stars who either are male or live in Miami ( have Miami as a part of their address).
Quiz (1) Answer a) SELECT address FROM studio WHERE name = MGM; b) SELECT birthdate FROM moviestar WHERE name = Sandra Bullock; c) SELECT starName FROM StarsIn WHERE movieYear = 1980 OR movieTitle LIKE %Love%; d) SELECT name FROM MovieExec WHERE netWorth >= 10,000,000; e) SELECT name FROM MovieStar WHERE gender = M OR address LIKE % Miami %;
LIKE Operator The LIKE operator is used to search for a specified pattern in a column.. SQL LIKE Syntax: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern Persons Table P_ID LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger Ex. Want to select persons living in city ending with "s" from "Persons" table. (% can be used to define wildcards (missing letters in pattern)) Sol: SELECT * FROM Persons WHERE City LIKE %s';
SELECT TOP Stmt TOP clause is used to specify the number of records to return. Can be useful on large tables with 000s of records as Returning a large number of records can impact on performance. SQL TOP Syntax: SELECT column_name(s) FROM table_name WHERE ROWNUM <= number Ex : Want to select only the two first records in the table above Sol: SELECT * FROM Persons Where RowNum<2
Union VS Union ALL The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows.
IN Operator Nested Queries SELECT Model FROM Product WHERE ManufacturerID IN (SELECT ManufacturerID FROM Manufacturer WHERE Manufacturer = 'Dell') The nested query above will select all models from the Product table manufactured by Dell:
Aggregation Functions Perform calculation on a set of values and return single value. Syntax Select Function(column) fromtable Name Salary Mohamed 4000 Hassan 3000 Doaa 6000 Ahmed 4000
Aggregation Functions Requirements Select Stmt Result Average of salary Select AVG(salary) from employees; 4250 Number of Rows Select COUNT(*) from employees; 4 Distinct Salaries Select COUNT(Distinct Salary) from employees; 3 Highest Salary Select MAX(salary) from employees; 6000 Lowest Salary Select MIN(salary) from employees; 3000 Total Salaries Select SUM(salary) from employees; 17000
Group By Clause The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
Group By Clause Syntax SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
Example Orders Table O_Id Order_Date Order_Price Customers 1 2013/11/12 1000 Hansen 2 2013/03/12 1600 Nilsen 3 2013/05/02 700 Hansen 4 2013/07/09 300 Hansen 5 2013/08/01 2000 Jensen 6 2013/04/03 100 Nilsen Now we want to find the total sum (total order) of each customer.
Example Sol SELECT Customer,SUM(Order_Price) FROM Orders GROUP BY Customer; Customer Sum(Order_Price) Hansen 2000 Nilsen 1700 Jinsen 2000 PS: When using the same select stmt without Group By clause it will fire an error.
Example Ex: Sells (bar, beer, price) Find average price for each peer. Sol: Select Beer, AVG(Price) From Sells Group By Beer; Ex: Get the name of the dept and its No. of Emp that make over 25,000$ per year. Sol: Select department, COUNT(*) as 'Number of Employees' From Employees Where salary > 25000 Group by department
Having Clause The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. Also, HAVING is used in conjunction with the SELECT clause to specify a search condition for a group. The HAVING clause behaves like the WHERE clause, but is applicable to groups. 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
Example Ex: want to find if any of the customers have a total order of less than 2000. Sol: SELECT Customer,SUM(OrderPrice) FROM Orders S GROUP BY Customer HAVING SUM(OrderPrice)<2000 Ex: want to find if the customers Hansen or Jensen have a total order of more than 1500. Sol: SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Hansen' OR Customer='Jensen' GROUP BY Customer HAVING SUM(OrderPrice)>1500
Example SELECT COUNT(*) FROM EMPLOYEES GROUP BY ID HAVING SALARY > 15000; this query is illegal, because the column SALARY is not a grouping column, it does not appear within an aggregate, and it is not within a subquery; SELECT COUNT(*) FROM EMPLOYEES GROUP BY ID HAVING SUM(SALARY) > 15000; Aggregates in the HAVING clause do not need to appear in the SELECT list
Diff. btw. Having & Where 1. HAVING specifies a search condition for a group or an aggregate function used in SELECT statement. The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause. 2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping. 3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

More Related Content

Dynamic websites lec2

  • 1. Dynamic Websites PHP with Oracle DB By Belal Arfa
  • 2. Section 2 In this section will discuss 1- Examples on last section 2- Quiz 3- Like Operator 4- Top Stmt 5- Union VS Union ALL 6- IN Operator 7- Aggregation Functions 8- Group By 9- Having 10- Difference Between Having and Where
  • 3. Quiz (1) Relations: Movie(title, year, length, inColor, studioName, producerC#) StarsIn(movieTitle, movieYear, starName) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, netWorth) Studio(name, address, presC#) Queries: a) Find the address of MGM studios. b) Find Sandra Bullocks birthdate. c) Find all the stars that appear either in a movie made in 1980 or a movie with Love in the title. d) Find all executives worth at least $10,000,000. e) Find all the stars who either are male or live in Miami ( have Miami as a part of their address).
  • 4. Quiz (1) Answer a) SELECT address FROM studio WHERE name = MGM; b) SELECT birthdate FROM moviestar WHERE name = Sandra Bullock; c) SELECT starName FROM StarsIn WHERE movieYear = 1980 OR movieTitle LIKE %Love%; d) SELECT name FROM MovieExec WHERE netWorth >= 10,000,000; e) SELECT name FROM MovieStar WHERE gender = M OR address LIKE % Miami %;
  • 5. LIKE Operator The LIKE operator is used to search for a specified pattern in a column.. SQL LIKE Syntax: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern Persons Table P_ID LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger Ex. Want to select persons living in city ending with "s" from "Persons" table. (% can be used to define wildcards (missing letters in pattern)) Sol: SELECT * FROM Persons WHERE City LIKE %s';
  • 6. SELECT TOP Stmt TOP clause is used to specify the number of records to return. Can be useful on large tables with 000s of records as Returning a large number of records can impact on performance. SQL TOP Syntax: SELECT column_name(s) FROM table_name WHERE ROWNUM <= number Ex : Want to select only the two first records in the table above Sol: SELECT * FROM Persons Where RowNum<2
  • 7. Union VS Union ALL The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows.
  • 8. IN Operator Nested Queries SELECT Model FROM Product WHERE ManufacturerID IN (SELECT ManufacturerID FROM Manufacturer WHERE Manufacturer = 'Dell') The nested query above will select all models from the Product table manufactured by Dell:
  • 9. Aggregation Functions Perform calculation on a set of values and return single value. Syntax Select Function(column) fromtable Name Salary Mohamed 4000 Hassan 3000 Doaa 6000 Ahmed 4000
  • 10. Aggregation Functions Requirements Select Stmt Result Average of salary Select AVG(salary) from employees; 4250 Number of Rows Select COUNT(*) from employees; 4 Distinct Salaries Select COUNT(Distinct Salary) from employees; 3 Highest Salary Select MAX(salary) from employees; 6000 Lowest Salary Select MIN(salary) from employees; 3000 Total Salaries Select SUM(salary) from employees; 17000
  • 11. Group By Clause The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
  • 12. Group By Clause Syntax SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
  • 13. Example Orders Table O_Id Order_Date Order_Price Customers 1 2013/11/12 1000 Hansen 2 2013/03/12 1600 Nilsen 3 2013/05/02 700 Hansen 4 2013/07/09 300 Hansen 5 2013/08/01 2000 Jensen 6 2013/04/03 100 Nilsen Now we want to find the total sum (total order) of each customer.
  • 14. Example Sol SELECT Customer,SUM(Order_Price) FROM Orders GROUP BY Customer; Customer Sum(Order_Price) Hansen 2000 Nilsen 1700 Jinsen 2000 PS: When using the same select stmt without Group By clause it will fire an error.
  • 15. Example Ex: Sells (bar, beer, price) Find average price for each peer. Sol: Select Beer, AVG(Price) From Sells Group By Beer; Ex: Get the name of the dept and its No. of Emp that make over 25,000$ per year. Sol: Select department, COUNT(*) as 'Number of Employees' From Employees Where salary > 25000 Group by department
  • 16. Having Clause The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. Also, HAVING is used in conjunction with the SELECT clause to specify a search condition for a group. The HAVING clause behaves like the WHERE clause, but is applicable to groups. 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
  • 17. Example Ex: want to find if any of the customers have a total order of less than 2000. Sol: SELECT Customer,SUM(OrderPrice) FROM Orders S GROUP BY Customer HAVING SUM(OrderPrice)<2000 Ex: want to find if the customers Hansen or Jensen have a total order of more than 1500. Sol: SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Hansen' OR Customer='Jensen' GROUP BY Customer HAVING SUM(OrderPrice)>1500
  • 18. Example SELECT COUNT(*) FROM EMPLOYEES GROUP BY ID HAVING SALARY > 15000; this query is illegal, because the column SALARY is not a grouping column, it does not appear within an aggregate, and it is not within a subquery; SELECT COUNT(*) FROM EMPLOYEES GROUP BY ID HAVING SUM(SALARY) > 15000; Aggregates in the HAVING clause do not need to appear in the SELECT list
  • 19. Diff. btw. Having & Where 1. HAVING specifies a search condition for a group or an aggregate function used in SELECT statement. The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause. 2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping. 3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.