Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
CS122A,Introduction to Data Management
Consider the following relation: Flights (fno INT, distance INT, price REAL)
1. Write a SQL query to find the price and distance of the cheapest flight, not less than 100 dollars, for each distance with at least 2 such flights, sorted by distance, in the increasing order.
2. Given the below table, what is the result of your query from question 1.
|
fno |
distance |
price |
|
1 |
500 |
40 |
| 2 |
500 |
50 |
| 3 |
500 |
50 |
| 4 |
200 |
200 |
| 5 | 50 | 150 |
| 6 | 50 | 150 |
| 7 | 40 | 250 |
| 8 | 40 | 350 |
3. Write a SQL query to authorize a user called “Lilith” to delete records from a Flights table and also let her authorize other database users to delete records from the table as well.
GRANT__________________ ON__________________TO__________ ________ _____ ___________ _______;
4. Suppose that a user “Cedric” receives an authorization from, but not only from, the user “Lilith” to delete records from Flights. If the DELETE privilege is revoked only from Lilith, can Cedric continue to delete records from Flights? (a) YES (b) NO
5. Create a view of the Flights table, with those having a price more than 40 dollars and less than 200 dollars and not equal to 150 dollars. Include the flight number, origin, and destination.
(You may assume origin and destination columns are also given with varachar(20) datatype).
CREATE VIEW ________________________________ (__________________________________________) AS
SELECT____________________________________________________
FROM _______________________________________________
WHERE ______________________________________________________________________________________
6. Suppose we have a table Dept(did, budget, empCount). We also have a view
CREATE VIEW BigDept(did, budget, empCount) AS
SELECT * FROM Dept where empCount > 100;
We want to make the view’s content always consistent with the content of the base table. Write a rowlevel trigger to enforce this consistency for INSERT statements into the Depttable.
CREATE TRIGGER (_____________________________________)
AFTER INSERT ON (_______________________________________)
FOR EACH (_________________________________________)
BEGIN
IF (___________________________________________________) THEN
___________________________________________________________________
___________________________________________________________________
___________________________________________________________________
END IF
END
7. Consider a relational table R(A, B, C, D, E, F) with a set of functional dependencies: A>B, B>C,CD>E, and AD>F. Is AD a candidate key? Why?