Blog

Based on the following ER Diagram from the database called “StudentFinancial.” What SQL statement would you use to answer the following question? Image Long Description The “Student” entity is connected to the “Tuition” entity with a one-to-relationship labeled “Pays”. Student Entity and Attributes PSUID INT (primary key) FirstName VARCHAR(45) LastName VARCHAR(45) Email VARCHAR(45) Department VARCHAR(45) Tuition Entity and Attributes tuitionID INT (primary key) Student INT (foreign key) SemesterCost INT Scholarships INT DueDate INT List all students (first name and last name) from the Biology department and only from the Biology department.  SELECT [attributes]FROM [table]WHERE [condition]

Based on the following ER Diagram from the database called “StudentFinancial.” What SQL statement would you use to answer the following question? Image Long Description The “Student” entity is connected to the “Tuition” entity with a one-to-relationship labeled “Pays”. Student Entity and Attributes PSUID INT (primary key) FirstName VARCHAR(45) LastName VARCHAR(45) Email VARCHAR(45) Department VARCHAR(45) Tuition Entity and Attributes tuitionID INT (primary key) Student INT (foreign key) SemesterCost INT Scholarships INT DueDate INT How many students share the same due date? SELECT [attributes]FROM [table][clause]

In a database called “Restaurants,” what is the SQL command to create the following table? Image Long Description The pizzatoppings entity contains the following attributes and data types: toppings_id INT PRIMARY KEY calories INT servingSize VARCHAR(45)

Based on the following ER Diagram from the database called “StudentFinancial.” What SQL statement would you use to answer the following question? Image Long Description The “Student” entity is connected to the “Tuition” entity with a one-to-relationship labeled “Pays”. Student Entity and Attributes PSUID INT (primary key) FirstName VARCHAR(45) LastName VARCHAR(45) Email VARCHAR(45) Department VARCHAR(45) Tuition Entity and Attributes tuitionID INT (primary key) Student INT (foreign key) SemesterCost INT Scholarships INT DueDate INT What are the email addresses of all students who have a scholarship of less than 5000?  SELECT [attributes]FROM [table]ON [join]WHERE  [condition]

Show all movies where the title starts with “B” but does not start with “Bo”. Order by title (A-Z). Image Long Description Results Grid idmovies title genre year director rating numrating 28 Back to the Future Drama 1985 Robert Zemeckis 9 250000 23 Beyond the Sea Drama 2004 Kevin Spacey 7 13091 6 Blade Runner Thriller 1982 Ridley Scott 9 484746 25 Braveheart Drama 1995 Mel Gibson 8 81328 NULL NULL NULL NULL NULL NULL NULL

Show the top 10 highest-rated movies. Use LIMIT. Order by rating in descending order. Image Long Description Results Grid idmovies title genre year director rating numrating 26 Seven Samurai Drama 1954 Akira Kurosawa 10 75049 35 Selma Drama 2014 Ava DuVernay 10 853049 13 The Shawshank Redemption Crime 1994 Frank Darabont 10 999486 34 The Matrix Thriller 1999 Lana Wachowski 9 940593 4 The Deer Hunter Drama 1978 Michael Cimino 9 905335 5 Amadeus Drama 1984 Milos Forman 9 558043 28 Back to the Future Drama 1985 Robert Zemeckis 9 250000 8 The Usual Suspects Crime 1995 Bryan Singer 9 779489 9 Chinatown Mystery 1974 Roman Polanski 9 227235 17 Deliverance Adventure 1972 John Boorman 9 870394

Show all movies that are either in the “Comedy” or “Action” genre and have more than 100,000 ratings. Use IN to determine genre. Order by the number of ratings, high to low. Image Long Description Results Grid idmovies title genre year director rating numrating 29 Swept Away Comedy 1974 Lina Wertmuller 7 650984 22 Aliens Action 1986 James Cameron 8 511613 30 American Splendor Comedy 2003 Shari Springer Berman 7 399205 11 Annie Hall Comedy 1977 Woody Allen 8 203875 32 Wayne’s World Comedy 1992 Penelope Spheeris 7 203551 NULL NULL NULL NULL NULL NULL NULL

Display all movies directed by Quentin Tarantino, Martin Scorsese, or Christopher Nolan. Order by director (A-Z). Image Long Description Results Grid idmovies title genre year director rating numrating 19 The Prestige Thriller 2006 Christopher Nolan 7 586328 NULL NULL NULL NULL NULL NULL NULL

List all unique genres in the database (A-Z). Image Long Description Results Grid genre Action Adventure Animation Comedy Crime Drama Horror Mystery Romance Thriller

What values for CountryID do the suppliers Sugarplum and Periwinkle have after the UPDATE statement executes? Supplier SupplierID CompanyName ContactName CountryID 2 Oshkosh Bgosh Martina Perry 2 3 LittleGuppies Cierra Martinez 1 4 Sugarplum Miranda Adams NULL 5 Rosebuds for Girls Adan Stevens 2 6 Lin Ming C. Linda Ming 3 7 Periwinkle Sarah Gomez NULL UPDATE SupplierSET CountryID = 2WHERE CountryID IS NULL;