SQL Test/Quiz Questions
How many directors have more than 4 movies that have an imdb rating of greater than 8.
10. SELECT person_name, COUNT(*) AS movies FROM sqlmdb.persons INNER JOIN sqlmdb.movie_jobs USING (person_guid) INNER JOIN sqlmdb.movies USING (movie_guid) WHERE imdb_rating > 8 AND job_code = 'DRTR' HAVING COUNT(*) > 4 GROUP BY person_name;
What is the approximate average runtime for movies from the 1950s? Hint: The release_year should be between 1950 and 1959.
116
How many movies are in the top 10 of either American Film Institute (AFI) Top 100 lists (1997 or 2007).
12. SELECT movie_title FROM sqlmdb.movies WHERE afi_top100_1997 <= 10 OR afi_top100_2007 <= 10;
How many movies have a metascore from Metacritic.com of 80 or over? Hint: The CRITIC_REVIEWS table includes the metascore attribute.
134. SELECT COUNT(movie_guid) FROM sqlmdb.movies INNER JOIN sqlmdb.critic_reviews USING (movie_guid) WHERE metascore >= 80;
How many taglines are associated with the movie "The Matrix"?Hint: The tagline attribute is in the TAGLINES table.
15.
What is the total worldwide gross of movies starring Bruce Willis? Hint: Use the SUM aggregation function.
1620855662. SELECT SUM(worldwide_gross) FROM sqlmdb.movies INNER JOIN sqlmdb.movie_actors USING (movie_guid) INNER JOIN sqlmdb.persons USING (person_guid) WHERE person_name = 'Bruce Willis';
How many movies have a perfect Tomatometer rating? Hint: Find the Tomatometer attribute in the ROTTEN_TOMATOES table.
19. SELECT COUNT(movie_guid) FROM sqlmdb.movies INNER JOIN sqlmdb.rotten_tomatoes USING (movie_guid) WHERE tomatometer = 100;
What is the approximate average number of movies made by across all studios?
2.2 SELECT AVG(number_of_movies) FROM (SELECT studio_name, COUNT(*) AS number_of_movies FROM sqlmdb.studios INNER JOIN sqlmdb.movie_studios USING (studio_guid) GROUP BY studio_name) sub GROUP BY 1;
How many constructed languages (type C) are available in the database? Hint: Use the LANGUAGES table.
22. SELECT DISTINCT language_a3code FROM sqlmdb.languages WHERE language_type = 'C';
Director Sergio Leone famously makes very long movies. What is the runtime of the longest film he directed? Hint: Use the MOVIE_JOBS table and job_code DRTR (for director).
229. SELECT movie_title, runtime FROM sqlmdb.movies INNER JOIN sqlmdb.movie_jobs USING (movie_guid) INNER JOIN sqlmdb.persons USING (person_guid) WHERE person_name = 'Sergio Leone' ORDER BY runtime DESC;
How many movies have a perfect score (100) for the Metascore (from Metacritic.com) or the Tomatometer (from Rotten Tomatoes). Hint: the CRITIC_REVIEWS table has the metascore attribute and the ROTTEN_TOMATOES table has the tomatormeter attribute. Movies may have only one score so careful when considering any joins!
25. SELECT movie_guid, movie_title FROM sqlmdb.movies FULL JOIN sqlmdb.critic_reviews USING (movie_guid) FULL JOIN sqlmdb.rotten_tomatoes USING (movie_guid) WHERE metascore = 100 OR tomatometer = 100;
How many movies have titles that start with "The "? Hint: Use the LIKE operator.
259. SELECT COUNT(*) FROM (SELECT movie_title FROM sqlmdb.movies WHERE movie_title LIKE 'The %') sub;
How many movies have an IMDb rating over 8.5 and IMDb votes over 500,000? Hint: The attributes imdb_rating and imdb_votes can be found in the MOVIES table.
27. SELECT COUNT(movie_guid) FROM sqlmdb.movies WHERE imdb_rating > 8.5 AND imdb_votes > 500000;
How many movies have nearly perfect scores (>= 99) for the Metascore (from Metacritic.com) and the Tomatometer (from Rotten Tomatoes). Hint: the CRITIC_REVIEWS table has the metascore attribute and the ROTTEN_TOMATOES table has the tomatormeter attribute.
3. SELECT COUNT(*) FROM (SELECT movie_title FROM sqlmdb.movies FULL JOIN sqlmdb.critic_reviews USING (movie_guid) FULL JOIN sqlmdb.rotten_tomatoes USING (movie_guid) WHERE metascore >= 99 AND tomatometer >= 99) sub;
How many movie industry participants (PERSONS) come from the country of Denmark? Hint: Use the BIRTH_COUNTRY_A3CODE in the PERSONS table (the code for Denmark is "DNK").
49. SELECT person_name FROM sqlmdb.persons WHERE birth_country_a3code = 'DNK';
What is the approximate average worldwide gross for movies directed by Steven Spielberg? Hint: The movie tally is shown as well in the answers.
494807015 (5? 6) SELECT AVG(worldwide_gross), COUNT(movie_title) FROM sqlmdb.movies INNER JOIN sqlmdb.movie_jobs USING (movie_guid) INNER JOIN sqlmdb.persons USING (person_guid) WHERE person_name = 'Steven Spielberg' AND job_code = 'DRTR';
How many movies were made between 2000 and 2009. Hint: Use the release_year attribute of the MOVIES table.
51. SELECT COUNT(movie_guid) FROM sqlmdb.movies WHERE release_year BETWEEN 2000 AND 2009;
How many movies include cast members (MOVIE_ACTORS) from Denmark? Hint: Use the BIRTH_COUNTRY_A3CODE in the PERSONS table (with code "DNK" for Denmark). Careful to handle duplicate movies in the result set.
53. SELECT COUNT(*) AS total FROM (SELECT DISTINCT movie_title FROM sqlmdb.movies INNER JOIN sqlmdb.movie_actors USING (movie_guid) INNER JOIN sqlmdb.persons USING (person_guid) WHERE birth_country_a3code = 'DNK' GROUP BY movie_title) sub;
How many people have been actors and directors? Hint: Use the MOVIE_JOBS table (with job_code DRTR) and MOVIE_ACTORS table.
61 SELECT person_guid, person_name FROM sqlmdb.persons WHERE (person_guid IN (SELECT person_guid FROM sqlmdb.movie_actors)) AND (person_guid) IN (SELECT person_guid FROM sqlmdb.movie_jobs WHERE job_code = 'DRTR');
How many studios have the word "Disney" in the studio name? Hint: Use the STUDIOS table.
7
What is the average IMDb rating for films from the 1960s? Hint: The release_year should be between 1960 and 1969.
7.9 SELECT AVG(imdb_rating) FROM sqlmdb.movies WHERE release_year BETWEEN 1960 AND 1969;
What is average metascore (approximate) of movies starring Tom Hanks? Hint: The table CRITIC_REVIEWS contains the metascores.
82.67. SELECT person_name, AVG(metascore) FROM sqlmdb.persons INNER JOIN sqlmdb.movie_actors USING (person_guid) INNER JOIN sqlmdb.critic_reviews USING (movie_guid) WHERE person_name LIKE '%Tom Hanks%' GROUP BY person_name;
How many actors have acted in 20 or more (>= 20) movies? Hint: Use the MOVIE_ACTORS table as the starting point.
9
How many movies have a worldwide_gross over 1 billion (1,000,000,000) dollars?
9. SELECT movie_title, worldwide_gross FROM sqlmdb.movies WHERE worldwide_gross > 1000000000;
How many movies are associated with the country of France? Hint: France has a country_a3code of FRA in the MOVIE_COUNTRIES table.
91. SELECT COUNT(*) FROM (SELECT movie_guid FROM sqlmdb.movies INNER JOIN sqlmdb.movie_countries USING (movie_guid) WHERE country_a3code = 'FRA') sub;
Optimizers can use the following factor(s) to compare the costs of alternative execution plans.
A combination of disk I/O, CPU usage, and memory usage
Please select the best indexing option for the query and partial schema below. Note: Assume all primary keys are already indexed. Categories(CategoryID [PK], CategoryName)Books(BookID [PK], BookTitle, ISBN, YearPublished, PublisherID [FK1], CategoryID [FK2])Authors(AuthorID [PK], AuthorName, DateOfBirth, DateOfDeath)Roles(RoleID [PK], RoleName)BookAuthors(BookID [PK/FK1], AuthorID [PK/FK2], RoleID [FK3]) Find the books written by a specific author (by author name). SELECTbook_title,year_published,release_yearFROMauthorsINNER JOIN book_authorsUSING (author_id)INNER JOIN booksUSING (book_id)WHERE author_name LIKE 'E% Poe';
CREATE INDEX authors_name_ix ON authors (author_name);
Please select the best indexing option for the query and partial schema below. Note: Assume all primary keys are already indexed. Categories(CategoryID [PK], CategoryName)Books(BookID [PK], BookTitle, ISBN, YearPublished, PublisherID [FK1], CategoryID [FK2])Authors(AuthorID [PK], AuthorName, DateOfBirth, DateOfDeath)Roles(RoleID [PK], RoleName)BookAuthors(BookID [PK/FK1], AuthorID [PK/FK2], RoleID [FK3]) Find the authors for a specific book title (LIKE 'One Hundred Years of Solitude%'). SELECT author_nameFROMauthorsINNER JOIN book_authorsUSING (author_id)INNER JOIN booksUSING (book_id)WHERE book_title LIKE 'One Hundred Years of Solitude%';
CREATE INDEX books_title_ix ON books (book_title);
Please select the best indexing option for the query and partial schema below. Note: Assume all primary keys are already indexed. Movies(MovieGUID [PK], MovieTitle, ReleaseYear, IMDbRating, IMDbVotes)Persons(PersonGUID [PK], PersonName, BirthYear, DeathYear)Countries(CountryA3Code [PK], CountryName) List all the movie industry participants born in a given country (a3code = 'ESP' for Spain), along with the birth year. SELECTperson_name,country_name,birth_yearFROMrmdb.persons pLEFT OUTER JOIN rmdb.countries cON p.birth_country_a3code = c.country_a3codeWHERE p.birth_country_a3code IN ('ESP')ORDER BY country_name, birth_year;
CREATE INDEX countries_country_ix ON countries (country_a3code);
Please select the best indexing option for the query and partial schema below. Note: Assume all primary keys are already indexed. Movies(MovieGUID [PK], MovieTitle, ReleaseYear, IMDbRating, IMDbVotes)RottenTomatoes(MovieGUID [PK/FK1], tomatometer, reviewer_tally) Lookup basic movie data by matching movie title (LIKE 'F% Gump'). SELECTmovie_guid,movie_title,release_yearFROM moviesWHERE movie_title LIKE 'F% Gump';
CREATE INDEX movies_title_ix ON movies (movie_title);
Please select the best indexing option for the query and partial schema below. Note: Assume all primary keys are already indexed. .... WHERE (birth_year = 1970) AND (p.birth_country_a3code IN ('ESP'));
CREATE INDEX persons_bc_by__ix ON persons (birth_country_a3code, birth_year)
Please select the best indexing option for the query and partial schema below. Note: Assume all primary keys are already indexed. Movies(MovieGUID [PK], MovieTitle, ReleaseYear, IMDbRating, IMDbVotes)Persons(PersonGUID [PK], PersonName, BirthYear, DeathYear)Countries(CountryA3Code [PK], CountryName) Find all the movie industry participants born in a given year (= 1970). SELECTperson_name,country_nameFROMrmdb.persons pLEFT OUTER JOIN rmdb.countries cON p.birth_country_a3code = c.country_a3codeWHERE birth_year = 1970;
CREATE INDEX persons_birth_year_ix ON persons (birth_year);
Please select the group of DDL CREATE TABLE statements that best implements the partial schema shown below. Books(BookID [PK], BookTitle, ISBN, YearPublished, ...)Authors(AuthorID [PK], AuthorName, DateOfBirth, DateOfDeath)Roles(RoleID [PK], RoleName)BookAuthors(BookID [PK/FK1], AuthorID [PK/FK2], RoleID [FK3])
CREATE TABLE books ( book_id NUMBER, book_title VARCHAR2(100), isbn VARCHAR2(50), year_published NUMBER, CONSTRAINT books_pk PRIMARY KEY (book_id)); CREATE TABLE authors ( author_id NUMBER, author_name VARCHAR2(50), date_of_birth DATE, date_of_death DATE, CONSTRAINT authors_pk PRIMARY KEY (author_id)); CREATE TABLE roles ( role_id NUMBER, role_name VARCHAR2(50), CONSTRAINT roles_pk PRIMARY KEY (role_id)); CREATE TABLE book_authors ( book_id NUMBER, author_id NUMBER, CONSTRAINT book_authors_pk PRIMARY KEY (book_id, author_id), CONSTRAINT ba_book_id_fk FOREIGN KEY (book_id) REFERENCES books (book_id), CONSTRAINT ba_author_id_fk FOREIGN KEY (author_id) REFERENCES authors (author_id), CONSTRAINT ba_role_id_fk FOREIGN KEY (role_id) REFERENCES roles (role_id));
Please select the group of DDL CREATE TABLE statements that best implements the partial schema shown below. Customers(CustomerID [PK], CustomerName, Email, Address)Orders(OrderID [PK], OrderDate, ShipDate, Priority, CustomerID [FK1], ShipperID [FK2])Shippers(ShipperID [PK], ShipperName, Email, Address, URL)
CREATE TABLE customers ( customer_id NUMBER, customer_name VARCHAR2(50), email VARCHAR2(100), address VARCHAR2(100), CONSTRAINT customers_pk PRIMARY KEY (customer_id)); CREATE TABLE orders ( order_id NUMBER, order_date DATE, ship_date DATE, priority NUMBER, customer_id NUMBER, shipper_id NUMBER, CONSTRAINT orders_pk PRIMARY KEY (order_id), CONSTRAINT orders_customer_id_fk FOREIGN KEY (customer_id) REFERENCES customers (customer_id), CONSTRAINT orders_shipper_id_fk FOREIGN KEY (shipper_id) REFERENCES shippers (shipper_id)); CREATE TABLE shippers ( shipper_id NUMBER, shipper_name VARCHAR2(50), email VARCHAR2(100), address VARCHAR2(100), url VARCHAR2(100), CONSTRAINT shippers_pk PRIMARY KEY (shipper_id));
Please select the group of DDL CREATE TABLE statements that best implements the partial schema shown below. Employees(EmployeeID [PK], FirstName, LastName, Address)EmployeeEmails(EmployeeID [PK/FK1], Email, RecoveryEmail)EmployeeFingerprints(EmployeeID [PK/FK1], Fingerprint)
CREATE TABLE employees ( employee_id NUMBER, first_name VARCHAR2(50), last_name VARCHAR2(50), address VARCHAR2(100), CONSTRAINT employees_pk PRIMARY KEY (employee_id)); CREATE TABLE employee_emails ( employee_id NUMBER, email VARCHAR2(100), recovery_email VARCHAR2(100), CONSTRAINT employee_emails_pk PRIMARY KEY (employee_id), CONSTRAINT ee_employee_id_fk FOREIGN KEY (employee_id) REFERENCES employees (employee_id)); CREATE TABLE employee_fingerprints ( employee_id NUMBER, fingerprint BLOB, CONSTRAINT employee_fingerprints_pk PRIMARY KEY (employee_id), CONSTRAINT ef_employee_id_fk FOREIGN KEY (employee_id) REFERENCES employees (employee_id));
Please select the group of DDL CREATE TABLE statements that best implements the partial schema shown below. Project(ProjectID [PK], ProjectName, Description)Skill(SkillID [PK], SkillName)ProjectSkill(ProjectID [PK/FK1], SkillID [PK/FK2])
CREATE TABLE project ( project_id NUMBER, project_name VARCHAR2(50), description VARCHAR2(100), CONSTRAINT project_pk PRIMARY KEY (project_id)); CREATE TABLE skill ( skill_id NUMBER, skill_name VARCHAR2(50), CONSTRAINT skill_pk PRIMARY KEY (skill_id)); CREATE TABLE project_skill ( project_id NUMBER, skill_id NUMBER, CONSTRAINT project_skill_pk PRIMARY KEY (project_id, skill_id), CONSTRAINT ps_project_id_fk FOREIGN KEY (project_id) REFERENCES project (project_id), CONSTRAINT ps_skill_id_fk FOREIGN KEY (skill_id) REFERENCES skill (skill_id));
Create a basic e-commerce database that handles order for products from customers. Products should be associated with suppliers (more than one supplier for a given product). Products should include a SKU, name, description and price. Products should be organized into categories to support sales analysis. Customer can place many orders and each order can include many products (or items). Orders should be associated with shippers that deliver orders. Customer information includes name, e-mail and physical address. Pick the best schema from the alternatives presented.
Categories(CategoryID [PK], CategoryName, Description) Products(ProductID [PK], SKU, ProductName, Description, Price, CategoryID [FK1]) Suppliers(SupplierID [PK], SupplierName, Email, Address, URL) ProductSuppliers(ProductID [PK/FK1], SupplierID [PK/FK2], IsPrimary) Customers(CustomerID [PK], CustomerName, Email, Address) Shippers(ShipperID [PK], ShipperName, Email, Address, URL) Orders(OrderID [PK], OrderDate, ShipDate, Priority, CustomerID [FK1], ShipperID [FK2]) OrderItems(OrderID [PK/FK1], ProductID [PK/FK2], Price, Quantity, Discount)
What is the top movie based on the American Film Institute (AFI) Top 100 list from 1997? Hint: Use the attribute afi_top100_1997 in the MOVIES table.
Citizen Kane
Create a database to support recruitment by tracking a person's employment history. You should be able to generate basic reports like listing the positions held by an employee at a company (including start and end dates). The projects worked on while at the company, as well as skills used should also be captured.
Company(CompanyID [PK], CompanyName, Description) Employee(EmployeeID [PK], FirstName, LastName, Address, Email) Postion(PositionID [PK], PostionName) Project(ProjectID [PK], ProjectName, Description) Skill(SkillID [PK], SkillName) ProjectSkill(ProjectID [PK/FK1], SkillID [PK/FK2]) JobHistory(EmployeeID [PK/FK1], CompanyID [PK/FK2], PositionID [PK/FK3], ProjectID [PK/FK4], StartDate, EndDate, Description, Achievements]
Please select the best indexing option for the query and partial schema below. Note: Assume all primary keys are already indexed. Breweries(BreweryID [PK], BreweryName, Address, Country, URL)Beers(BeerID [PK], BeerName, ABV, BreweryID [FK1], CategoryID [FK2], StyleID [FK3]) Count the strong (AVB > 12) beers. SELECT COUNT(*)FROM beerdb.beersWHERE abv > 12;
Create INDEX beers_beer_id_ix ON beers (beer_id);
Design a database to support a small restaurant that wants to track reservations. This simple reservation system could be the start of platform that many restaurants might use with integrations to services like OpenTable. The basic functionality should allow a restaurant to track their customers, including the reservations made and menu items ordered. Each menu item should include a list of the main ingredients for customers to browse at their convenience. Ingredients are categorized to support reporting and analysis. A reservation includes both the time, table number and the size of the party. The restaurant staff and their roles should be included as well, including the staff members that handle each reservation since customers can leave comments based on their experience.
Customers(CustomerID [PK], CustName, Email, Phone, CustSinceDate) Tables(TableNbr [PK], Capacity, IsWindow) Roles(RoleCode [PK], RoleName, Description) StaffMembers(StaffID [PK], StaffName, Email, Phone, RoleCode [FK1]) Reservations(ResID [PK], ResDate, PartySize, IsCanceled, CustomerID [FK1], TableNbr [FK2], StaffID [FK3]) Categories(CatCode [PK], CatName) Ingredients(IngredientID [PK], IngredientName, CatCode [FK1]) MenuItems(MenuItemID [PK], Description, Price) MenuItemIngredients(MenuItemID [PK/FK1], IngredientID [PK/FK2], Quantity) ReservationOrders(ResID [PK/FK1], MenuItemID [PK/FK2], Quantity)
Design a database for a no-frills online bank that offers different types of accounts to customers. The bank does have a few physical branches to offer advice and handle more complicated transactions. Customers can have one or more accounts, which can be shared with others as a joint account. One customer is designated as the primary account holder. Several different types of accounts are offered such as checking, savings and retirement accounts. All transactions for an account must be captured, including the transaction date, amount, resulting balance and transaction type (such as deposit or withdrawal). Transactions should also be grouped into different expense categories to support reporting and analysis. Please select the schema that best fits these requirements.
Customers(CustomerID [PK], CustomerName, Email, Phone, CustomerSinceDate, AddressID [FK1], BranchID [FK2]) Branches(BranchID [PK], BranchName, BusinessHours, AddressID [FK1]) Addresses(AddressID [PK], AddressLine1, AddressLine2, PostalCode, City, StateProvince, Country) AccountTypes(AccountTypeID [PK], AccountTypeName) Accounts(AccountID [PK], AccountName, OpenDate, CloseDate, Balance, AccountTypeID [FK1]) CustomerAccounts(CustomerID [PK/FK1], AccountID [PK/FK2], IsPrimary) TransactionTypes(TxnTypeID [PK], TxnTypeName) ExpenseCategories(ExpCategoryID [PK], ExpCategoryName) Transactions(TransactionID [PK], TransactionDate, Notes, Amount, Balance, AccountID [FK1], TxnTypeID [FK2], ExpCategoryID [FK3])
Please design a database to support a small group of SCUBA diving instructors that wish to keep track of their students. The goal is to build an app that students can use to document their diving experiences. The main entities will certainly include the newly trained divers, the dives completed and the instructors themselves. The divers earn certifications sponsored by national or international diving organizations like PADI or NAUI (offering various levels of certifications). The dives are tracked by dive sites along with date and duration information. Dive sites a grouped into categories to support reporting and analysis. Special sites such as shipwrecks include historical details. The goal is to allow divers to keep an online log of all their dives. Please select the database schema that best meets the requirements.
Divers(DiverID [PK], DiverName, Email, Phone) Organizations(OrgID[PK], OrgName, OrgURL) Certifications(CertCode [PK], CertTitle, Description, OrgID [FK1]) Instructors(InstructorID [PK], InstructorName, Email, Phone) DiverCertifications(DiverID [PK/FK1], CertCode [PK/FK2], StartDate, CertDate, InstructorID [FK3]) SiteCategories(SiteCatID [PK], CatName, Description) Sites(SiteID [PK], SiteName, Description, SiteCatID [FK1]) Shipwrecks(SiteID [PK/FK1], WreckDate, Description) Dives(DiverID [PK/FK1], SiteID [PK/FK2], DiveDate [PK], Duration, IsNightDive, Notes)
Design a database for tracking traffic accidents. The date, location and type of accident should be captured. The drivers involved and police officers at the scene should be stored. Of course, drivers and officers can be involved in multiple accidents. Vehicles should include model and manufacturer information from standardized lists. Documents related to the accident such as insurance cards and vehicle registrations should be stored, including a document scan. Select the schema that best meets the requirements.
Drivers(DriverID [PK], DriverName, LicenseNbr, State) Officers(OfficerID [PK], OfferName, BadgeNbr) Accidents(AccidentID [PK], AccidentDate, Location, AccidentTypeID [FK1]) AccidentTypes(AccidentTypeID [PK], AccidentTypeName) DriversInAccidents(DriverID [PK/FK1], AccidentID [PK/FK2]) OfficersAtAccidents(OfficerID [PK/FK1], AccidentID [PK/FK2]) Vehicles(VehicleID [PK], Year, ModelCode [FK1]) VehiclesInAccidents(VehicleID [PK/FK1], AccidentID [PK/FK2]) Manufacturers(ManufacturerCode [PK], ManufacturerName) Models(ModelCode [PK], ModelName, ManufacturerCode [FK1]) Documents(DocumentID [PK], DocumentScan, DocumentTypeID [FK1], AccidentID [FK2]) DocumentTypes(DocumentTypeID [PK], DocumentTypeName)
A GROUP BY clause should always be included when using an aggregation function (e.g., SUM, MIN).
False
A bitmap index is relatively inexpensive to update when changes are made to the underlying table's data one row at a time.
False
A database is considered "normalized" if only the structured query language (SQL) is used to retrieve data in normal ways.
False
All columns in a table are not null, unless otherwise specified.
False
Bitmap indexes are typically the default index structure in most database systems.
False
Collecting database statistics for optimization is a very inexpensive operation.
False
Database optimizers must generate execution plans that reflect the exact order of operations specified in the SQL statement.
False
Decreasing the size of shared memory for the database buffer cache can often dramatically improve performance.
False
Given a books table with columns named book_title and year _released, the following SQL statement will select the number of books released each year. SELECT book_title, year_released FROM books GROUP BY year_released;
False
Given a books table with the column year_released, the following will obtain all books released from 2000 (exclusive) to 2010 (inclusive). SELECT * FROM books WHERE year_released BETWEEN 2000 AND 2010
False
Given an authors table with the column last_name, the following will only obtain authors with a last name starting with 'J'. SELECT * FROM authors WHERE last_name LIKE '%J';
False
Indexes are cost-free mechanisms that dramatically improve the performance of a database.
False
Indexes are mandatory structures and must be associated with all database tables.
False
Integrity constraints are automatically generated by the database management system.
False
It is equally important to index small tables and large tables.
False
Only SELECT and DELETE statements can specify WHERE clause.
False
Providing transaction-level read consistency is simpler and less costly in terms of performance than statement-level read consistency.
False
SQL 3-valued logic means a result may be true, false, or possible in some alternative universe.
False
SQL is only capable of selecting, changing, removing, and updating data in a database.
False
Selectivity describes the ability of the optimizer to identify the most expensive queries.
False
Selectivity refers to the process by which the optimizer determines which users have access to various database objects.
False
Table-level locking reduces the possibility of contention for data items compared to row-level locking.
False
The ACID property "atomicity" refers to new molecular-level database computers.
False
The ACID property "durability" refers to the "five nines" or 99.999% uptime.
False
The Oracle DBMS allows multiple transactions to hold exclusive locks on the same row at the same time.
False
The ROLLBACK -9 command can be used to undo any changes even after a COMMIT.
False
The first query is computationally less expensive than the second query. Query 1: SELECT DISTINCT(release_date) FROM books; Query 2: SELECT release_date FROM books;
False
There is really no difference between conceptual and logical database designs.
False
This is a design fragment from a database to support recruitment by tracking a person's employment history. You should be able to generate basic reports like listing the positions held by an employee at a company (including start and end dates). Is this design in third normal form (3NF)? Company(CompanyID [PK], CompanyName, Description) Employee(EmployeeID [PK], FirstName, LastName, Address, Email) Postion(PositionID [PK], PostionName) JobHistory(EmployeeID [PK/FK1], CompanyID [PK/FK2], PositionID [PK/FK3], CompanyName, StartDate, EndDate, Description, Achievements)
False
This is a design fragment from a hotel bookings database that supports booking rooms and rating the hotels. Assume all the rooms are similar, so we just need a count of the rooms booked. Is this design in third normal form (3NF)? Hotels(HotelID [PK], HotelName, Address, URL) Guests(GuestID [PK], GuestName, Address) Bookings(BookingID [PK], HotelID [FK1], GuestID [FK2], CheckInDate, CheckOutDate, RoomTally) HotelRatings(HotelID [PK/FK1], GuestID [PK/FK2], GuestName, Rating, Comment)
False
This is a design fragment is from a database to support concert ticket sales. Assume concerts are associated with just a single artist. Artists are organized by genres to support customer searches. Concerts are held at venues, which provide directions from alternative locations. Is this design in third normal form (3NF)? Genres(GenreID [PK], GenreName) Artists(ArtistID [PK], GenreID [FK], ArtistName, URL) Venues(VenueID [PK], VenueName, Address, URL) Concerts(ConcertID [PK], ArtistID [FK1], VenueID [FK2], ConcertTitle, StartDatetime, EndDatetime, VenueName)
False
This is a design fragment is from a database to support concert ticket sales. Assume concerts are associated with just a single artist. Artists are organized by genres to support customer searches. Concerts are held at venues, which provide directions from alternative locations. Is this design in third normal form (3NF)? Genres(GenreID [PK], GenreName) Artists(ArtistID [PK], VenueID [PK], GenreID [FK], ArtistName, URL, GenreName) Venues(VenueID [PK], VenueName, Address, URL) Concerts(ConcertID [PK], ArtistID [FK1], VenueID [FK2], ConcertTitle, StartDatetime, EndDatetime)
False
When joining multiple tables together, all joins must be of the same type.
False
Whenever a table is created, all indexes (e.g., primary key, foreign key(s), lookup column(s)) are also automatically created.
False
A CROSS JOIN is commonly used to join two tables that match a specified condition.
False.
A RIGHT OUTER JOIN displays all tuples from two tables (left and right), regardless of whether corresponding tuples are found in either table.
False.
SQL 3-valued logic means a result may be true, false, or unknown (often arising from comparisons with NULL values).
False.
How many movies are "Fresh" or "Rotten" according to Rotten Tomatoes? Hint: Use the tomatometer_status in the ROTTEN_TOMATOES table.
Fresh: 132 SELECT COUNT(*) FROM sqlmdb.rotten_tomatoes INNER JOIN sqlmdb.movies USING (movie_guid) WHERE tomatometer_status = 'Fresh'; Rotten: 4 SELECT COUNT(*) FROM sqlmdb.rotten_tomatoes INNER JOIN sqlmdb.movies USING (movie_guid) WHERE tomatometer_status = 'Rotten';
A B+-tree index includes pointers to link the leaf nodes for which reason(s)?
In order to improve range query performance
Design a catalog database to track the book inventory for a small system of libraries. Information to be captured includes books, book authors, publishers and the libraries themselves. Of course, books can have many authors and libraries have many books. Books are grouped into basic literature categories. Authors are assigned roles to clarify their authorship contributions. Select the schema that best meets these requirements.
Libraries(LibraryID [PK], LibraryName, Address, ZIPCode, CityID [FK1]) Publishers(PublisherID [PK], PublisherName, Address, ZIPCode, CityID [FK1]) Categories(CategoryID [PK], CategoryName) Books(BookID [PK], BookTitle, ISBN, YearPublished, PublisherID [FK1], CategoryID [FK2]) LibraryBooks(LibraryID [PK/FK1], BookID [PK/FK2]) Authors(AuthorID [PK], AuthorName, DateOfBirth, DateOfDeath) Roles(RoleID [PK], RoleName) BookAuthors(BookID [PK/FK1], AuthorID [PK/FK2], RoleID [FK3]) Countries(CountryID [PK], CountryName) Cities(CityID [PK], CityName, Population, CountryID [FK1])
Which film is the highest rated Romantic Comedy (in both the Romance and Comedy genres) based on the IMDb rating? Hint: The genre_code attributes ROM (Romance) and COM (Comedy), hence the term ROM-COM.
Life is Beautiful (1997)
Design a database for managing the vehicle for small auto dealers. Track the make and model information (with production years), along with the specifics for individual cars and trucks. Data should include the year, price, color and options or trim packages for the vehicle. In addition, track the type of incentives available for each vehicle. Consider the schema options and select the best design for the requirements.
Makes(MakeID [PK], MakeName) Models(ModelID [PK], ModelName, FirstProductionYear, LastProductionYear) Vehicles(VehicleID [PK], Year, Price, ModelID [FK1], ColorID [FK2], PackageID [FK3]) Colors(ColorID [PK], ColorName, ColorCode) Packages(PackageID [PK], PackageName, Description) Incentives(IncentiveID [PK], Type, Amount, Terms) VehicleIncentives(VehicleID [PK/FK1], IncentiveID [PK/FK2], Expiration Date)
Design a music database focused on albums and tracks (songs), both of which can be reviewed by reviewers. A review consists of a rating and optional comment. For reviewers just record a name and enrollment date. Of course, an album can have many tracks. However each album is associated with a single studio and a genre. An album is created by a group (an artist or band). Let's not worry about group collaborations or invited performers. This is just a preliminary design. Review the schema options and select the best fit. Group of answer choices
Musicians(MusicianID [PK], FirstName, LastName, BirthDate, BirthCountry) Groups(GroupID [PK], GroupName) MusicianGroups(MusicianID [PK/FK1], GroupID [PK/FK2], JoinDate, DepartureDate) Albums(AlbumID [PK], AlbumTitle, ReleaseYear, GroupID [FK1], StudioID [FK2], GenreID [FK3]) Tracks(AlbumID [PK/FK1], TrackNbr [PK], TrackTitle, Length) Studios(StudioID [PK], StudioName, Location) Genres(GenreID [PK], GenreName) Reviewers(ReviewerID [PK], ReviewerName, EnrollmentDate) AlbumReviews(AlbumID [PK/FK1], ReviewerID [PK/FK2], Rating, Comment) TrackReviews(AlbumID [PK/FK1], TrackNbr [PK/FK1], ReviewerID [PK/FK2], Rating, Comment)
B+-tree indexes are balanced for which reason?
Query performance/retrieval is kept predictable.
Design a database for managing cafe menus and orders (assume only one location). Servers take orders by table based on the menu of the day, including the quantity and a note. For shopping purposes, track the ingredients used including quantities and units of measure (for replenishment). Consider the schema options and select the best design for the requirements.
Servers(ServeID [PK], ServerName) Orders(OrderID [PK], OrderDate, TableNbr, ServerID [FK1]) Menus(MenuID [PK], MenuDate) MenuItems(MenuItemID [PK], MenuItemName, Description, Price, MenuID[FK1]) Ingredients(IngredientID [PK], IngredientName) MenuItemIngredients(MenuItemID [PK/FK1], IngredientID [PK/FK2], Quantity, Units) OrderMenuItems(OrderID [PK/FK1], MenuItemID [PK/FK2], Quantity, Note)
Which Action genre movie is liked the most by professional critics (as measured by the metascore from Metacritic.com)? Hints: The genre_code ACT identifies the Action genre. The metascore attribute is in the CRITIC_REVIEWS table.
Seven Samurai (1954). SELECT movie_guid, movie_title, metascore FROM sqlmdb.movies INNER JOIN sqlmdb.critic_reviews USING (movie_guid) INNER JOIN sqlmdb.movie_genres USING (movie_guid) WHERE genre_code = 'ACT' ORDER BY metascore DESC;
Which film is the highest rated Action genre movie using the IMDb rating? Hint: The genre_code ACT identifies the Action genre.
The Dark Knight (2009). SELECT movie_title, imdb_rating FROM sqlmdb.movies INNER JOIN (sqlmdb.movie_genres) using (movie_guid) WHERE genre_code = 'ACT' ORDER BY imdb_rating DESC;
What is the movie title and release year of the top film based on IMDb rating?
The Shawshank Redemption (1994)
A domain is the set of permitted values for an attribute of an entity.
True
A good database design allows as many important queries to be answered as possible.
True
A physical database design specifies the detailed physical configuration of database objects on the underlying storage media.
True
A physical design focuses on the detailed storage architecture of a database, including indexing and data placement on storage media.
True
A primary key is composed of one or more attributes that uniquely identifies all individual entities in an entity set.
True
A relation is in first normal form if it has no repeating groups and composite fields.
True
A relation is in second normal form if it is in first normal form and no non-key attribute in the relation is functionally dependent on a proper subset of any candidate key.
True
A tablespace is a logical storage structure that maps to one or more physical data files.
True
An important decisions an optimizer makes is the selection of index-based access methods.
True
An inner subquery may be evaluated only once since there is no linkage (or correlation) with the outer query.
True
Anything in shared memory must be considered volatile and would vanish during a database crash!
True
As an underlying table's data changes, any associated indexes may also require changes.
True
B+-tree indexes are suitable for high cardinality attributes.
True
B+-tree indexes are typically the default index structure in most database systems.
True
B+-tree indexes must be continually re-balanced as the underlying tables change.
True
Bitmap indexes are very useful in reporting databases and data warehouses.
True
Comparing a NULL value to another NULL value will result in an unknown value.
True
Composite (or multi-column) indexes can be used to improve the performance of targeted queries by avoiding computations of unions.
True
Cost-based optimization (CBO) relies on current statistics to produce good execution plans.
True
Database normalization is the process of organizing the fields and relations of a relational database to minimize redundancy and dependency.
True
Database optimizers often provide a choice of optimizing for throughput or response time.
True
Database updates can only be considered persistent when the information is written to external data files (safely on secondary storage).
True
Function-based indexes offer the ability to pre-compute and store calculated attributes for improved performance.
True
Given a books table with columns named book_title and year _released, the following SQL statement will select the number of books released each year. SELECT year_released, COUNT (book_title) FROM books GROUP BY year_released;
True
Given a books table with the column genre, the following will obtain all books with a genre of 'Horror' or 'True Crime'. SELECT * FROM books WHERE genre IN ('Horror', 'True Crime');
True
Given a books table with the column year_released, the following will obtain all books released from 2000 to 2010. SELECT * FROM books WHERE year_released BETWEEN 2000 AND 2010
True
Good query optimization was a critical factor in the commercial success of relational database technologies.
True
In most situations, a DBMS will automatically create an index when a primary key is defined.
True
In the Oracle DBMS, primary and unique keys generall have indexes created automatically, but you might want to create an index on a foreign key as well.
True
In the Oracle DBMS, queries can be automatically re-written by the optimizer to take advantage of materialized views.
True
Index structures can use a lot of storage space in a database.
True
Integrity constraints provide a rich mechanism for expressing business rules in a database, thereby protecting data quality.
True
Isolation (one of the ACID properties) means that each transaction executes as if it were the only transaction, insulated from the effects of other currently executing transactions.
True
It is often reasonable to create an index when a query is likely to retrieve a small fraction of the rows in a table.
True
Joins are among the most expensive database operations
True
Many DBMSs incorporate a mechanism for providing direct guidance or "hints" to the optimizer.
True
Materialized views can improve query execution because the view results have already been physically instantiated.
True
Materialized views can often be used to improve the efficiency of query execution.
True
Multiple attributes (or columns) can be indexed together in a single structure.
True
One benefit of concurrent transactions is that small transactions need not wait for large transactions to complete before being executed (assuming both transactions do not require exclusive locks on the same data item(s)).
True
Read consistency guarantees that the data being manipulated during a statement or transaction remains consistent, even if two transactions are using the same data.
True
Sorting is a relatively common (and potentially costly) algorithm used to process database queries.
True
Statement-level read consistency means that each user sees a consistent view of the data, including changes made by committed transactions from other users.
True
Statement-level read consistency means that queries will read data that is consistent during each query.
True
The ACID property "atomicity" refers to transactions being handled as an atomic unit of work: either executed completely or not at all.
True
The ACID property "durability" refers to the persistency of any changes made by a committed transaction, despite possible database failures.
True
The ACID property "isolation" refers to a transaction being executed as if it is the only transaction in the database, without interference from any other concurrently executing transactions.
True
The COMMIT command saves changes to persistent disk.
True
The Oracle DBMS always enforces at least statement-level read consistency and guarantees that all the data returned by a single query comes from a single point in time.
True
The Oracle DBMS may resolve deadlocks by rolling back the work of one of the processes.
True
The cache hit ratio refers to the fraction of logical I/O requests that are resolved in memory, rather than from physical disk reads.
True
The data dictionary is a collection of database tables and views containing metadata about the database objects, users, privileges, and other operational information.
True
The data dictionary is typically accessed during query and transaction processing.
True
The database writer processes (DBWn) periodically write buffers to advance the checkpoint, which is the position in the redo log from which instance recovery is initiated.
True
The decision of whether to use constraints can have an impact on data quality.
True
The entity-relationship (ER) model is a widely used data model for database design.
True
The following two SELECT statements will display the same result set. SELECT book_title, publisher FROM books, publishers WHERE books.publisher_id = publishers.publisher_id; SELECT book_title, publisher FROM books INNER JOIN publishers ON (books.publisher_id = publishers.publisher_id);
True
The more indexes there are on a table, the more overhead is incurred as the underlying table is modified.
True
The most common relationships in the ER model are binary relationships.
True
The redo log buffer (or write ahead log) is used to re-construct changes made by recent transactions during database recovery.
True
The unique constraint allows null values.
True
Third normal form is violated when a non-key field is a fact about another non-key field.
True
This is a design fragment from a database for a small network of medical clinics, perhaps for concierge physician group. Patients enroll in the group and are able to schedule visits with a specific doctor as needed. Each doctor is associated with one of the clinics in the network. Medications and medical procedures may be associated with each visit. Is this design in third normal form (3NF)? Clinics(ClinicID [PK], AddressID [FK], ClinicName) Doctors(DoctorID [PK], ClinicID [FK], DoctorName, Email, Phone) Patients(PatientID [PK], PatientName, Gender, DateOfBirth, EnrollmentDate) Addresses(AddressID [PK], AddressLine1, AddressLine2, ZIPCode) Visits(VisitID [PK], PatientID [FK1], DoctorID [FK2], VisitDateTime, VisitCost)
True
This is a design fragment from a database to support recruitment by tracking a person's employment history. You should be able to generate basic reports like listing the positions held by an employee at a company (including start and end dates). Is this design in third normal form (3NF)? Company(CompanyID [PK], CompanyName, Description) Employee(EmployeeID [PK], FirstName, LastName, Address, Email) Postion(PositionID [PK], PostionName) Project(ProjectID [PK], ProjectName, Description) JobHistory(EmployeeID [PK/FK1], CompanyID [PK/FK2], PositionID [PK/FK3], ProjectID [PK/FK4], StartDate, EndDate, Description, Achievements)
True
This is a design fragment from a hotel bookings database that supports booking rooms and rating the hotels. Assume all the rooms are similar, so we just need a count of the rooms booked. Is this design in third normal form (3NF)? Hotels(HotelID [PK], HotelName, Address, URL) Guests(GuestID [PK], GuestName, Address) Bookings(BookingID [PK], HotelID [FK1], GuestID [FK2], CheckInDate, CheckOutDate, RoomTally) HotelRatings(HotelID [PK/FK1], GuestID [PK/FK2], Rating, Comment)
True
UNION ALL is a computationally less expensive operation than UNION since duplicates are not removed.
True
When mapping a conceptual to logical design, a many-to-many relationship involves creating a new associative relational table with keys drawn from both related tables.
True
A correlated subquery may be evaluated for each row in the main query, implementing row-by-row processing.
True.
An INNER JOIN is commonly used to join two tables that match a specified condition (the most widely used JOIN).
True.
Assume the SQL statement below is valid (all tables and columns exist). This SELECT statement will show all book titles regardless of whether a movie based on that book is also present in the movies table. SELECT book_title, movie_title FROM books LEFT OUTER JOIN movies ON (books.book_id = movies.book_id);
True.
Assume the following relation: authors_books (author_id, book_id). The following SQL statement will select only the IDs of authors that have published at least 10 books. SELECT author_id FROM authors_books GROUP BY author_id HAVING COUNT(*) >= 10;
True.
Design a simple photo sharing database to support an app. Users should be able to post photos to albums. Assume photos are in only one album for now. All photos have a single location. Users can tag photos using existing tags. Users can comment on photos. Users can follow other users and get notifications about new posts. Please select the schema that best fits this scenario.
Users(UserID [PK], UserName, Email, Phone) Followers(FollowerID [PK/FK1], FolloweeID [PK/FK2], CreatedDate) Albums(AlbumID [PK], AlbumTitle, Description, CreatedDate, UserID [FK1]) Locations(LocationID [PK], LocationName, Latitude, Longitude) Photos(PhotoID [PK], PhotoTitle, PostedDate, AlbumID [FK1], LocationID [FK2]) Comments(UserID [PK/FK1], PhotoID [PK/FK2], PostedDate [PK], Comment) Tags(TagID [PK], TagTitle) PhotoTags(PhotidID [PK/FK1], TagID [PK/FK2], TaggedDate)
