SQL Database Questions PDF

# SQL Database Questions - Place all answers in the answer sheet. Do not remove the staple. - Questions 1 to 12 refer to the following tables and data: ### Tables - **animal** (id(pk), name) - **type** (id(pk), type) - **animal_type** (id_animal(pk, fk), id_type (pk, fk)) **Foreign Keys:** -...

# SQL Database Questions - Place all answers in the answer sheet. Do not remove the staple. - Questions 1 to 12 refer to the following tables and data: ### Tables - **animal** (id(pk), name) - **type** (id(pk), type) - **animal_type** (id_animal(pk, fk), id_type (pk, fk)) **Foreign Keys:** - id_animal references animal(id) - id_type references type(id) ### Table Data #### animal | id | name | |---|---| | 1 | Pluto | | 2 | Daisy | | 3 | Donald | | 4 | Goofy | | 5 | Mickey | #### type | id | type | |---|---| | 10 | dog | | 20 | duck | | 30 | mouse | | 40 | cat | | 50 | fish | #### animal_type | id_animal | id_type | |---|---| | 1 | 10 | | 2 | 20 | | 3 | 20 | | 4 | 10 | | 5 | 30 | ## Questions **1.** Which of the following components of the SQL language is used to grant and revoke permissions on databases? a) The Data Manipulation Language (DML) b) The Data Definition Language (DDL) c) The Data Control Language (DCL) d) The Data Query Language (DQL) **2.** Which command gives the user Popeye permission to add new data into the table shows (which is in the database tv) without allowing him to delete any data. a) GRANT SELECT ON tv.shows TO 'Popeye'; b) GRANT ALL ON tv.shows TO 'Popeye'; c) GRANT ADD ON tv.shows TO 'Popeye'; d) GRANT INSERT ON tv.shows TO 'Popeye'; **3.** Which option should be added to the above command if you want to allow Popeye to extend his privileges to other users? a) WITH GRANT OPTION b) WITH CHECK OPTION c) WITH EXTEND OPTION d) WITH ROOT OPTION **4.** What does the following command display? SHOW GRANTS FOR 'Cat'; a) Lists the privileges for all the users in the database 'Cat'. b) Lists the privileges for all the users of the table 'Cat'. c) Lists the privileges for the user 'Cat'. d) An error because SHOW GRANTS is not a valid command. **5.** The above table is a) in First Normal Form (1NF) and also in Third Normal Form (3NF) b) in First Normal Form, but not in Third Normal Form c) in Third Normal Form, but not in First Normal Form d) neither in First Normal Form nor in Third Normal Form **6.** How many rows are returned by the following query? SELECT movie_title FROM movie WHERE movie_id = (SELECT MIN(LENGTH(movie_title)) FROM movie); a) 0 rows b) 1 row c) 3 rows d) 4 rows ### Questions 7 to 12 refer to the following tables and data: - **movie** (movie_id(pk), movie_title, production_id, production_name) #### Data | movie_id | movie_title | production_id | production_name | |---|---|---|---| | 2 | Hotel Transylvania | 1 | Columbia Pictures | | 6 | Surf's Up | 1 | Columbia Pictures | | 4 | Sing | 2 | Dentsu | | 13 | Trolls World Tour | 2 | Dentsu | | 19 | Howl's Moving Castle | 2 | Dentsu | | 1 | Abominable | 3 | DreamWorks | | 7 | The SpongeBob Movie | 4 | Paramount | | 3 | Onward | 5 | Walt Disney Pictures | **7.** What is the result of the following query? ```sql SELECT a.name, t.type FROM animal a RIGHT JOIN animal_type ON a.id = id_animal RIGHT JOIN type t ON t.id = id_type WHERE t.type = 'cat'; ``` a) empty set b) error c) NULL d) | name | type | |---|---| | Pluto | cat | | Daisy | cat | | Donald | cat | | Goofy | cat | | Mickey | cat | **8.** How many rows will be included in the view exam? ```sql CREATE VIEW exam AS SELECT name FROM animal WHERE id IN (SELECT id_animal FROM animal_type WHERE id_type IN (SELECT id FROM type WHERE type = 'dog')); ``` a) 0 rows b) 1 row c) 2 rows d) 5 rows **9.** What will the type table contain after the following command? ```sql UPDATE type SET type = CASE WHEN id >= 40 THEN 'bear' WHEN id >= 30 THEN 'snake' ELSE type END; ``` a) | id | type | |---|---| | 10 | dog | | 20 | duck | | 30 | snake | | 40 | bear | | 50 | snake | b) | id | type | |---|---| | 10 | dog | | 20 | duck | | 30 | snake | | 40 | snake | | 50 | snake | c) | id | type | |---|---| | 10 | null | | 20 | null | | 30 | snake | | 40 | bear | | 50 | snake | d) | id | type | |---|---| | 10 | null | | 20 | null | | 30 | snake | | 40 | snake | | 50 | snake | **10.** How many rows will be returned after the following block of commands is run? ```sql CREATE VIEW exam2 AS SELECT id, name FROM animal WHERE name > 'M' WITH CHECK OPTION; INSERT INTO exam2 (id, name) VALUES (6, 'Clarabelle'); SELECT * FROM exam2; ``` How many rows does this select return? a) 0 rows b) 1 row c) 2 rows d) 3 rows **Questions 11 and 12:** How many rows will be deleted by the following commands? Note: *check the constraints*. **11.** DELETE FROM type WHERE id = 10; a) 0 rows b) 1 row c) 2 rows d) 3 rows **12.** DELETE FROM type WHERE id > 30; a) 0 rows b) 1 row c) 2 rows d) 3 rows ### Questions 13 to 18 refer to the following database: - **job\_current** - contact\_id - title - salary - start\_date - **job\_desired** - contact\_id - title - salary\_low - salary\_high - years\_exp - **job\_listings** - job\_id - title - salary - description - available - **profession** - prof\_id - profession - zip\_code - city - state - status\_id - **my\_contacts** - contact\_id - last\_name - first\_name - spouse\_id - email - gender - birthday - prof\_id - zip\_code - status\_id - **contact\_interest** - contact\_id - interest\_id - **interests** - interest\_id - interest - **contact\_seeking** - contact\_id - seeking\_id - **seeking** - seeking\_id - seeking **13.** How many rows would be returned by the following command? ```sql SELECT first_name, profession FROM my_contacts JOIN profession ON my_contacts.prof_id = profession.prof_id; ``` Assuming my_contacts has 10 rows and profession has 5 rows. *Note that my_contacts (prof_id) is a foreign key that references profession (prof_id). The column prof_id cannot be NULL.* a) 5 rows b) 10 rows c) 20 rows d) 50 rows **14.** ```sql SELECT prof_id FROM profession UNION SELECT prof_id FROM my_contacts; ``` a) 5 rows b) 10 rows c) 15 rows d) 20 rows **For questions 15 to 21, choose the option that completes the SQL statements to do each of the following:** **15.** List the email addresses for all contacts at USF (emails ending in "@usf.edu"): ```sql SELECT email FROM my_contacts WHERE ``` a) SUBSTRING b) LEFT c) RIGHT d) LENGTH (email, 8) = '@usf.edu'; **16.** List all zip codes (from the my\_contacts table) and the number of contacts in each zip code. ```sql SELECT zip_code, COUNT(contact_id) FROM my_contacts ``` a) JOIN zip_code b) ORDER BY zip_code c) ORDER BY contact_id d) GROUP BY zip_code **17.** List the first\_name, city and state for all contacts. Include all cities in the result set, even those without any contacts. ```sql SELECT first_name, city, state FROM zip_code ``` a) JOIN b) OUTER JOIN c) LEFT JOIN d) RIGHT JOIN my_contacts ON my_contacts.zip_code = zip_code.zip_code; **18.** List all job titles in the job\_current and job\_desired tables, in only one column, named All Jobs. All job titles should be listed, including duplicates. ```sql SELECT title AS 'All Jobs' FROM job_desired ``` a) UNION b) JOIN c) UNION ALL d) AND SELECT title FROM job_current; **19.** Add a new interest to the interests table. The name of the interest is Books and the id is automatically generated. ```sql INSERT INTO interests ``` a) VALUES ('Books') b) VALUES (AUTO_INCREMENT, 'Books') c) (interest) VALUES ('Books') d) (interest) VALUES (NULL, 'Books') **Questions 20 and 21:** The field spouse\_id in my\_contacts, stores the id for a contacts' spouse (who is also a record in my\_contacts). This is a 1:1 relationship (monogamous): each person can be married to at most one other person. **20.** List all married couples: ```sql SELECT mc1.first_name, mc2.first_name FROM my_contacts mc1 JOIN my_contacts mc2 ON ``` a) mc1.spouse_id = mc2.spouse_id b) mc1.spouse_id = mc2.contact_id c) mc1.spouse_id = mc1.contact_id d) mc1.contact_id = mc2.contact_id **21.** What should you add to the above command if you wanted each couple to appear only once in the result set? For example Homer & Marge is the same couple as Marge & Homer, so it should appear only once. a) WHERE mc1.contact_id <= mc2.contact_id b) WHERE mc1.contact_id < mc2.contact_id c) WHERE mc1.spouse_id = mc2.spouse_id d) WHERE mc1.spouse_id != mc2.spouse_id *Short answer: Please write all answers on the answer sheet.* **For questions 22 to 24, complete the SQL statements to do each of the following:** **22.** Change the data in the job_desired table to increase all salary_high values by 10%. ```sql salary_high = ``` **23.** Rename the column birthday (from the my_contacts table) to birthdate. ```sql ALTER TABLE my_contacts ``` **24.** Remove all the data in the table job_listings, without removing the table from the database. ```sql DELETE FROM ``` **25.** Columns in a table can also be called fields or ______. **26.** A primary key composed of multiple columns is called a ________ key. **27.** Complete the statement below so that it returns exactly: Lisa ```sql SELECT SUBSTRING('Homer-Marge-Lisa-Bart-Maggie', 13, ______ ); ``` **28.** Complete the statement below so that it returns exactly: Lisa-Bart ```sql SELECT ______ ('Lisa', '-', 'Bart'); ``` **29.** What is the result of the following command? ```sql SELECT SUBSTRING_INDEX('Aye-Aye, captain!', 'ye', 1); ``` **For questions 30 to 33, refer to the following ER diagram:** - **Employees** (ssd, salary, phone, dno) - **Departments** (dname, budget) Relationships: * **Employees** N:M **Departments** (*Works In*) * **Employees** 1:M **Dependents** * **Dependents** 1:M **Child** * **Child** has attributes *name* and *age* **30.** Name the entities represented in the diagram. **31.** How many attributes are represented? **32.** How many relationships are represented? **33.** When we transform this design into tables in 3NF (third normal form), how many total tables will we have? **For questions 34 to 42, refer to the following table exam and sample data:** - **exam** (id(pk), num, letter\_grade) #### Data | id | num | letter_grade | |---|---|---| | 1 | 55 | F | | 2 | 60 | F | | 3 | 65 | F | | 4 | 67 | F | | 5 | 70 | C | | 6 | 73 | C | | 7 | 75 | C | | 8 | 77 | C | | 9 | 78 | C | | 10 | 84 | B | | 11 | 85 | B+ | | 12 | 85 | B+ | | 13 | 87 | B+ | | 14 | 90 | A | | 15 | 92 | A | | 16 | 93 | A | | 17 | 94 | A | | 18 | 95 | A | | 19 | 96 | A+ | | 20 | 98 | A+ | **Questions 34 to 38: How many rows are in the result set of the following SQL statements? Note: *empty set = 0 rows*.** **34.** SELECT MIN(num) FROM exam GROUP BY letter_grade; **35.** SELECT letter_grade, COUNT(num) FROM exam GROUP BY letter_grade HAVING COUNT(num) < 4; **36.** SELECT id FROM exam WHERE letter_grade LIKE 'A_'; **37.** SELECT id FROM exam WHERE letter_grade LIKE 'B%'; **38.** SELECT id FROM exam WHERE num BETWEEN 95 AND 90; **Questions 39 to 42: What values appear in the result set of each of the following queries? Answer *error* if the query returns a syntax error.** **39.** SELECT id FROM exam WHERE num IN (93, 90); **40.** SELECT DISTINCT letter_grade FROM exam WHERE num < 73 OR letter_grade = 'B'; **41.** SELECT num FROM exam ORDER BY letter_grade, num DESC LIMIT 1; **42.** SELECT num FROM exam ORDER BY id WHERE letter_grade = 'C' LIMIT 1;