CH 7 - More SQL: Complex Queries, Triggers, Views, and Schema Modification

Question 1

How to check in SQL an attribute value is NULL?

Answer 1

• SQL uses the comparison operators IS or IS NOT

Question 2

7.4. Discuss how each of the following constructs is used in SQL, and discuss the various options for each construct. Specify what each construct is useful for. Nested queries

Answer 2

• nested queries, which are complete select-from-where blocks within another SQL query. That other query is called the outer query.
• These nested queries can also appear in the WHERE clause or the FROM clause or the SELECT clause or other SQL clauses as needed
• useful when you need to get information of different subqueries and then join
• useful when existing values in the database be fetched and then used in a comparison condition

Question 3

How to solve this? retrieve a PROJECT tuple if the PNUMBER value of that tuple is in a group A of tuples or group B of tuples

Answer 3

• Use a nested query

Question 4

EXISTS and UNIQUE

Answer 4

• Boolean functions that return TRUE or FALSE; hence, they can be used in a WHERE clause condition

Question 5

Query 6. Retrieve the names of employees who have no dependents.

Answer 5

SELECT Fname, Lname FROM EMPLOYEE WHERE
NOT EXISTS ( SELECT *
FROM DEPENDENT WHERE Ssn = Essn );

Question 6

What are Explicit Sets?

Answer 6

It is also possible to use an explicit set of values in the WHERE clause,

WHERE Pno IN (1, 2, 3);

Question 7

Query 23. Count the number of distinct salary values in the database.

Answer 7

SELECT COUNT (DISTINCT Salary)
FROM EMPLOYEE;

Question 8

7.3. Discuss how NULLs are treated in comparison operators in SQL. How are NULLs treated when aggregate functions are applied in an SQL query? How are NULLs treated if they exist in grouping attributes? 


Answer 8

• aggregate functions: NULL values are discarded when aggregate func- tions are applied to a particular column (attribute); the only exception is for COUNT(*) because tuples instead of values are counted.
• grouping: If NULLs exist in the grouping attribute, then a separate group is created for all tuples with a NULL value in the grouping attribute. F

Question 9

retrieve the names of all employees who have two or more dependents (Query 5),

Answer 9

SELECT Lname, Fname FROM EMPLOYEE
WHERE
( SELECT COUNT (*) 
FROM DEPENDENT WHERE
Ssn = Essn ) > = 2;

Question 10

Query 24. For each department, retrieve the department number, the number of employees in the department, and their average salary.

Answer 10

SELECT Dno, COUNT (*), AVG (Salary) FROM EMPLOYEE
GROUP BY
Dno;

Question 11

HAVING

Answer 11

Sometimes we want to retrieve the values of these functions only for groups that satisfy certain conditions.

Question 12

WHERE and HAVING clause

Answer 12

. The rule is that the WHERE clause is executed first, to select individual tuples or joined tuples; the HAVING clause is applied later, to select individual groups of tuples.

Question 13

For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than $40,000.

Answer 13

SELECT COUNT (*) FROM EMPLOYEE
WHERE Salary>40000 AND Dno IN 
( SELECT Dno FROM EMPLOYEE
GROUP BY Dno
HAVING COUNT (*) > 5)
GROUP BY
Dno,

Question 14

WITH

Answer 14

. The WITH clause allows a user to define a table that will only be used in a particular query;

Question 15

CASE

Answer 15

SQL also has a CASE construct, which can be used when a value can be different based on certain conditions. This can be used in any part of an SQL query where a value is expected, including when querying, inserting or updating tuples

Question 16

give employees different raise amounts depending on which department they work for; for example, employees in department 5 get a $2,000 raise, those in department 4 get $1,500 and those in department 1 get $3,000

Answer 16

UPDATE EMPLOYEE
SET Salary =
CASE 
WHEN Dno = 5 THEN Salary + 2000
WHEN Dno = 4 THEN Salary + 1500
WHEN Dno = 1  THEN Salary + 3000
ELSE Salary + 0 ;