Podcast
Questions and Answers
What is the purpose of the EXISTS function in SQL?
What is the purpose of the EXISTS function in SQL?
Which clause is necessary when using aggregate functions to summarize information?
Which clause is necessary when using aggregate functions to summarize information?
What does the UNIQUE function do in SQL?
What does the UNIQUE function do in SQL?
How is the GROUP BY clause typically structured in a query?
How is the GROUP BY clause typically structured in a query?
Signup and view all the answers
What is a correlated nested query?
What is a correlated nested query?
Signup and view all the answers
What is the use of the HAVING clause in an SQL statement?
What is the use of the HAVING clause in an SQL statement?
Signup and view all the answers
Which of the following aggregation functions would you use to find the smallest value in a set?
Which of the following aggregation functions would you use to find the smallest value in a set?
Signup and view all the answers
What is required when using nested queries in SQL?
What is required when using nested queries in SQL?
Signup and view all the answers
Study Notes
Lecture 5: More SQL Queries, Triggers, and Views
- Lecture focuses on complex SQL queries, triggers, and views
- Topics within the lecture include more complex SQL retrieval queries, triggers in SQL, and views
More Complex SQL Retrieval Queries
-
Nested queries: Complete
SELECT-FROM-WHEREblocks placed within theWHEREclause of other queries; used for filtering data based on results from other queries- Example Syntax:
SELECT Pnumber, Pname FROM project WHERE Pnumber IN (SELECT Pnumber FROM Project, Department WHERE dname = 'Finance');
- Example Syntax:
-
Nested queries (cont.): Employ comparison operators to compare values; avoiding potential errors and ambiguities via tuple variables (aliases) in SQL queries
- Example Syntax:
SELECT Lname, Fname FROM employee WHERE salary > ALL (SELECT salary FROM employee WHERE Dno = 5);
- Example Syntax:
-
Correlated nested queries: Nested queries using
=orINoperators, which can be collapsed into one single block- Example Syntax for Query 16A:
SELECT E.Fname, E.Lname FROM EMPLOYEE AS E, DEPENDENT AS D WHERE E.Ssn=D.Essn AND E.Sex=D.Sex AND E.Fname=D.Dependent_name;
- Example Syntax for Query 16A:
-
EXISTS and NOT EXISTS functions: Boolean functions returning
TRUEorFALSE; typically used with correlated nested queries to check if the result of a nested query is empty or not. TheNOT EXISTSfunction is typically used in conditions where a query needs to check whether a result does not exist.- Example use of
EXISTS:SELECT Fname, Lname FROM Employee WHERE EXISTS (SELECT * FROM DEPENDENT WHERE Ssn= Essn) AND EXISTS (SELECT * FROM Department WHERE Ssn= Mgr_Ssn);
- Example use of
Aggregate Functions
- Summarize information from multiple tuples into a single tuple
-
COUNT,SUM,MAX,MIN,AVGare built-in functions;- Example Syntax:
SELECT SUM(Salary), MAX (Salary), MIN (Salary), AVG (Salary) FROM EMPLOYEE;
- Example Syntax:
Grouping: The GROUP BY Clause
- Partitions the relation into subsets of tuples based on grouping attributes.
- Functions are applied to each group independently.
- The grouping attribute must appear in
SELECTclause- Example Syntax:
SELECT Dno, COUNT (*), AVG (Salary) FROM EMPLOYEE GROUP BY Dno;
- Example Syntax:
-
GROUP BYcan be used with joins- Example Syntax (with join):
SELECT Pnumber, Pname, COUNT (*) FROM PROJECT, WORKS_ON WHERE Pnumber=Pno GROUP BY Pnumber, Pname;
- Example Syntax (with join):
The GROUP BY and HAVING Clauses
-
HAVINGclause filters groups defined byGROUP BYclause. - Condition is applied to entire groups.
- Syntax includes
GROUP BYandHAVINGclauses -Example Syntax:sql SELECT Pnumber, Pname, COUNT (*) as Ccol FROM PROJECT, WORKS_ON WHERE Pnumber=Pno GROUP BY Pnumber, Pname HAVING Ccol > 2;
Use of Case
- Used to assign different values based on conditions in SQL queries
- Example Syntax:
UPDATE EMPLOYEE SET Salary = CASE WHEN Dno = 5 THEN Salary + 2000 WHEN Dno = 4 THEN Salary + 1500 WHEN Dno = 1 THEN Salary + 3000
Introduction to Triggers in SQL
-
CREATE TRIGGERstatements monitor the database - Triggers have three components (event, condition, action)
Use of Triggers
- Example trigger to check salary before insert or update
CREATE TRIGGER SALARY_VIOLATION
BEFORE INSERT OR UPDATE OF Salary, Supervisor_ssn ON
EMPLOYEE
FOR EACH ROW WHEN (NEW.SALARY > ( SELECT Salary
FROM EMPLOYEE WHERE Ssn = NEW. Supervisor_Ssn))
INFORM_SUPERVISOR (NEW.Supervisor.Ssn, New.Ssn)
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Related Documents
Description
This quiz covers the concepts from Lecture 5, focusing on complex SQL retrieval queries, triggers, and views. It explores nested queries, their syntax, and how to effectively use them in SQL. Enhance your understanding of SQL by testing your knowledge on these advanced topics.
