Data Management - Applications D427 - MYSQL - SQL Programing - Intermediate Level
San Francisco, CA 94110 USA , How many attributes are present in the address fragment?
4 Attributes
The Automobile table has the following columns: ID—integer, primary key, Make—variable-length string, Model—variable-length string, Year—integer, A new column must be added to the Automobile table: Column name: SafetyRating, Data type: decimal(3,1), Write a SQL statement to add the SafetyRating column to the Automobile table.
ALTER TABLE Automobile ADD SafetyRating DECIMAL(3,1);
The Book table has the following columns: ID—integer, primary key, auto_increment, Title—variable-length string, Genre—variable-length string, Year—integer , Write a SQL statement to delete the row with the ID value of 3 from the Book table.
DELETE from Book WHERE ID = 3;
Write the correct SQL statement to delete a database named testDB.
DROP DATABASE testDB;
Which restrictions applies when using a materialized view?
Materialized views require to be refreshed frequently and require more storage space.
The database contains a table named Movie. Write a SQL query to return all data from the Movie table without directly referencing any column names.
SELECT * From Movie;
List the number of customers in each country.
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
Define a SELECT statement?
SELECT column1, column2, ... FROM table_name;
Use the TRUNCATE statement to delete all data inside a table.
TRUNCATE TABLE Persons;
What does the SQL keyword command TRUNCATE do?
TRUNCATE removes all rows, unlike DELETE, which removes specific rows.
CREATE TABLE Invoice ( invoice_id INT NOT NULL AUTO_INCREMENT, date DATE NOT NULL, customer_id INT NOT NULL, PRIMARY KEY (invoice_id), FOREIGN KEY (customer_id) REFERENCES Customer (customer_id) ON DELETE SET TO NULL ); Looking at the Customer and Invoice tables and the CREATE TABLE for the Invoice table with foreign key reference statement above, what would happen to invoices in the Invoice table that are linked to a customer if that customer is deleted.
The Customer ID for those invoices would be changed to NULL.
CREATE TABLE Invoice ( invoice_id INT NOT NULL AUTO_INCREMENT, date DATE NOT NULL, customer_id INT NOT NULL, PRIMARY KEY (invoice_id), FOREIGN KEY (customer_id) REFERENCES Customer (customer_id) ON DELETE RESTRICT ); Looking at the Customer and Invoice tables and the CREATE TABLE for the Invoice table with foreign key reference statement above, what would happen to invoices in the Invoice table that are linked to a customer if that customer is deleted.
The delete of the Customer would not be allowed.
CREATE TABLE Invoice ( invoice_id INT NOT NULL AUTO_INCREMENT, date DATE NOT NULL, customer_id INT NOT NULL, PRIMARY KEY (invoice_id), FOREIGN KEY (customer_id) REFERENCES Customer (customer_id) ON DELETE CASCADE ); Looking at the Customer and Invoice tables and the CREATE TABLE for the Invoice table with foreign key reference statement above, what would happen to invoices in the Invoice table that are linked to a customer if that customer is deleted.
Those invoices would be deleted also.
The Package table has the following columns: Weight—decimal, Description—optional variable length string, LastChangedDate—date, TrackingNumber—integer, Which column should be designated the primary key for the Package table?
TrackingNumber
Update the City column of all records in the Customers table.
UPDATE Customers SET City = 'Oslo';
The Book table has the following columns: genre - varchar(20), pages - integer, author_id - char(3), isbn_number - varchar(20), Which column should be designated as the foreign key for the Book table?
author_id
The Book table has the following columns: genre - varchar(20), pages - integer, author_id - char(3), isbn_number - varchar(20), Which column should be designated at the primary key for the Book table?
isbn_number
The Book table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, Year—integer, The YearSales table has the following columns: Year—integer, TotalSales—bigint unsigned, Releases—integer, Write a SQL statement to designate the Year column in the Book table as a foreign key to the Year column in the TotalSales table.
ALTER TABLE Book ADD FOREIGN KEY (Year) REFERENCES YearSales (Year);
The Book table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, Year—integer, Write a SQL statement to modify the Book table to make the ID column the primary key.
ALTER TABLE Book ADD PRIMARY KEY (ID);
The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, The YearStats table has the following columns: Year—integer, TotalGross—bigint unsigned, Releases—integer, Write a SQL statement to designate the Year column in the Movie table as a foreign key to the Year column in the YearStats table.
ALTER TABLE Movie ADD CONSTRAINT Year FOREIGN KEY (Year) References YearStats(Year);
The Movie table has the following columns: ID—integer, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, Write a SQL statement to modify the Movie table to make the ID column the primary key.
ALTER TABLE Movie ADD PRIMARY KEY (ID);
The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, A new column must be added to the Movie table: Column name: Score, Data type: decimal(3,1), Write a SQL statement to add the Score column to the Movie table.
ALTER TABLE Movie ADD Score DECIMAL(3, 1);
Write sql query to allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns
ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrderFOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
Create a FOREIGN KEY constraint on the "PersonID" column in the "Orders" table
ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
Write a sql query to drop a FOREIGN KEY constraint
ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;
Add a column of type DATE called Birthday.
ALTER TABLE Persons ADD Birthday DATE;
Create a PRIMARY KEY constraint on the "ID" column on the table
ALTER TABLE Persons ADD PRIMARY KEY (ID);
Delete the column Birthday from the Persons table.
ALTER TABLE Persons DROP Birthday;
Write a sql query to drop a primary key constraint
ALTER TABLE Persons DROP PRIMARY KEY;
The Movie table has the following columns: ID - positive integer, Title - variable-length string, Genre - variable-length string, RatingCode - variable-length string, Year - integer, Write ALTER statements to make the following modifications to the Movie table: Add a Producer column with VARCHAR data type (max 50 chars). Remove the Genre column. Change the Year column's name to ReleaseYear, and change the data type to SMALLINT.
ALTER Table Movie ADD Producer Varchar(50), Drop Genre, CHANGE Year ReleaseYear SMALLINT;
Write the correct SQL statement to create a new database called testDB.
CREATE DATABASE testDB;
The Book table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, Year—integer, Write a SQL statement to create an index named idx_year on the Year column of the Book table
CREATE INDEX idx_year ON Book(Year);
The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, Write a SQL statement to create an index named idx_year on the Year column of the Movie table.
CREATE INDEX idx_year ON Movie (Year);
Write the correct SQL statement to create a new table called Persons.
CREATE TABLE ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
The Genre table has the following columns: GenreCode—variable-length string, primary key, GenreDescription—variable-length string, The Book table should have the following columns: Title—variable-length string, maximum 30 characters, GenreCode—variable-length string, maximum 5 characters, Write a SQL statement to create the Book table. Designate the GenreCode column in the Book table as a foreign key to the GenreCode column in the Genre table.
CREATE TABLE Book ( Title VARCHAR(30), GenreCode VARCHAR(5), FOREIGN KEY (GenreCode) REFERENCES Genre(GenreCode) );
The Customer table will have the following columns: CustomerID—positive integer, FirstName—variable-length string with up to 50 characters, MiddleInitial—fixed-length string with 1 character, LastName—variable-length string with up to 50 characters, DateOfBirth—date, CreditLimit—positive decimal value representing a cost of up to $19,999, with 2 digits for cents, Write a SQL statement to create the Customer table. Do not add any additional constraints to any column beyond what is stated.
CREATE TABLE Customer ( CustomerID INT UNSIGNED, FirstName VARCHAR(50), MiddleInitial CHAR(1), LastName VARCHAR(50), DateOfBirth DATE, CreditLimit DECIMAL(7,2) UNSIGNED );
Create a Horse table with the following columns, data types, and constraints. NULL is allowed unless 'not NULL' is explicitly stated. ID - integer with range 0 to 65 thousand, auto increment, primary key, RegisteredName - variable-length string with max 15 chars, not NULL, Breed - variable-length string with max 20 chars, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred, Height - number with 3 significant digits and 1 decimal place, must be ≥ 10.0 and ≤ 20.0, BirthDate - date, must be ≥ Jan 1, 2015,
CREATE TABLE Horse ( ID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, RegisteredName VARCHAR(15) NOT NULL, Breed VARCHAR(20) CHECK (Breed IN ('Egyptian Arab', 'Holsteiner', 'Quarter Horse', 'Paint', 'Saddlebred')), Height NUMERIC(3,1) CHECK (Height >= 10.0 AND Height <= 20.0), BirthDate DATE CHECK (BirthDate >= '2015-01-01') );
Two tables are created: Horse with columns: ID - integer, primary key, RegisteredName - variable-length string, Student with columns: ID - integer, primary key, FirstName - variable-length string, LastName - variable-length string, Create the LessonSchedule table with columns: HorseID - integer with range 0 to 65 thousand, not NULL, partial primary key, foreign key references Horse(ID), StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID), LessonDateTime - date/time, not NULL, partial primary key, If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically. If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.
CREATE TABLE LessonSchedule ( HorseID INT NOT NULL, StudentID INT, LessonDateTime DATETIME NOT NULL, PRIMARY KEY (HorseID, LessonDateTime), FOREIGN KEY (HorseID) REFERENCES Horse(ID) ON DELETE CASCADE, FOREIGN KEY (StudentID) REFERENCES Student(ID) ON DELETE SET NULL );
The Member table will have the following columns: ID—positive integer FirstName—variable-length string with up to 100 characters, MiddleInitial—fixed-length string with 1 character, LastName—variable-length string with up to 100 characters, DateOfBirth—date, AnnualPledge—positive decimal value representing a cost of up to $999,999, with 2 digits for cents, Write a SQL statement to create the Member table. Do not add any additional constraints to any column beyond what is stated.
CREATE TABLE Member ( ID INT PRIMARY KEY unsigned, FirstName VARCHAR(100), MiddleInitial CHAR(1), LastName VARCHAR(100), DateOfBirth DATE, AnnualPledge DECIMAL(8, 2) unsigned );
Create a Movie table with the following columns: ID - positive integer with maximum value of 50,000, Title - variable-length string with up to 50 characters, Rating - fixed-length string with 4 characters, ReleaseDate - date, Budget - decimal value representing a cost of up to 999,999 dollars, with 2 digits for cents,
CREATE TABLE Movie ( ID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, Title VARCHAR(50), Rating CHAR(4), ReleaseDate DATE, Budget DECIMAL(8, 2) CHECK (Budget >= 0 AND Budget <= 999999) );
The Rating table has the following columns: RatingCode—variable-length string, primary key, RatingDescription—variable-length string, The Movie table should have the following columns: Title—variable-length string, maximum 30 characters, RatingCode—variable-length string, maximum 5 characters, Write a SQL statement to create the Movie table. Designate the RatingCode column in the Movie table as a foreign key to the RatingCode column in the Rating table.
CREATE TABLE Movie ( Title VARCHAR(30), RatingCode VARCHAR(5), FOREIGN KEY (RatingCode) REFERENCES Rating(RatingCode) );
Create a FOREIGN KEY on the table Orders with OrderID as Primary KEY, and PersonID as the FOREIGN KEY.
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );
Write an SQL statement to allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );
Write a sql query to allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Person PRIMARY KEY (ID,LastName) );
Write a sql query to create a PRIMARY KEY on the "ID" column on the "Persons" table
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) );
Create a Student table with the following column names, data types, and constraints: ID - integer with range 0 to 65 thousand, auto increment, primary key, FirstName - variable-length string with max 20 chars, not NULL, LastName - variable-length string with max 30 chars, not NULL, Street - variable-length string with max 50 chars, not NULL, City - variable-length string with max 20 chars, not NULL, State - fixed-length string of 2 chars, not NULL, default "TX", Zip - integer with range 0 to 16 million, not NULL, Phone - fixed-length string of 10 chars, not NULL, Email - variable-length string with max 30 chars, must be unique,
CREATE TABLE Student( ID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(30) NOT NULL, Street VARCHAR(50) NOT NULL, City VARCHAR(20) NOT NULL, State CHAR(2) NOT NULL DEFAULT 'TX', Zip MEDIUMINT UNSIGNED CHECK (Zip >= 0 AND Zip <= 16000000) NOT NULL, Phone CHAR(10) NOT NULL, Email VARCHAR(30) UNIQUE );
Let's assume we have two tables, "employees" and "departments." We want to add a foreign key constraint on the "department_id" column in the "employees" table, which references the "department_id" column in the "departments" table.
CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(100) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100), department_id INT, -- Other employee-related columns... FOREIGN KEY (department_id) REFERENCES departments(department_id) );
The Book table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, Year—integer, Write a SQL statement to create a view named MyBooks that contains the Title, Genre, and Year columns for all movies books. Ensure your result set returns the columns in the order indicated.
CREATE VIEW MyBooks AS SELECT Title, Genre, Year FROM Book;
The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, Write a SQL statement to create a view named MyMovies that contains the Title, Genre, and Year columns for all movies. Ensure your result set returns the columns in the order indicated.
CREATE VIEW MyMovies AS SELECT Title, Genre, Year FROM Movie;
Create a CREATE VIEW statement, followed by the view name, the column names, and the SELECT query that defines the view's data.
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
Which SQL command is an example of data definition language (DDL)?
CREATE, ALTER, DROP, RENAME, TRUNICATE
Which data type will store "2022-01-10 14:22:12" as a temporal value without loss of information?
DATETIME
Which data type represents numbers with fractional values:
DECIMAL
Delete all the records from the Customers table where the Country value is 'Norway'.
DELETE FROM Customers WHERE Country = 'Norway';
Delete all the records from the Customers table.
DELETE FROM Customers;
The Horse table has the following columns: ID - integer, auto increment, primary key, RegisteredName - variable-length string, Breed - variable-length string, Height - decimal number, BirthDate - date, Delete the following rows: Horse with ID 5. All horses with breed Holsteiner or Paint. All horses born before March 13, 2013.
DELETE FROM Horse WHERE ID = 5; DELETE FROM Horse WHERE Breed IN ('Holsteiner', 'Paint'); DELETE FROM Horse WHERE BirthDate < '2013-03-13';
The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, Write a SQL statement to delete the row with the ID value of 3 from the Movie table.
DELETE FROM Movie WHERE ID = 3;
Define a DELETE statement?
DELETE FROM table_name WHERE condition;
Create query in a table to delete all rows for employees who's salary column is less than 80k.
DELETE FROM your_table_name WHERE salary < 80000;
Write the correct SQL statement to delete a table called Persons.
DROP TABLE Persons;
A database has a view named BookView. Write a SQL statement to delete the view named BookView from the database.
DROP VIEW BookView;
A database has a view named MovieView. Write a SQL statement to delete the view named MovieView from the database.
Drop View MovieView;
The Book table has the following columns: ID—integer, primary key, auto_increment Title—variable-length string, Genre—variable-length string, Year—integer, The following data needs to be added to the Book table: Title Genre Year, The Joy Luck Club, Fiction, 1989, Write a SQL statement to insert the indicated data into the Book table.
INSERT INTO Book (Title, Genre, Year) VALUES ('The Joy Luck Club', 'Fiction', 1989);
Insert a new record in the Customers table.
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
The Horse table has the following columns: ID - integer, auto increment, primary key, RegisteredName - variable-length string, Breed - variable-length string, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred, Height - decimal number, must be between 10.0 and 20.0, BirthDate - date, must be on or after Jan 1, 2015, Insert the following data into the Horse table: RegisteredName, Breed, Height, BirthDate, Babe, Quarter Horse, 15.3, 2015-02-10, Independence, Holsteiner, 16.0, 2017-03-13, Ellie, Saddlebred, 15.0, 2016-12-22, NULL, Egyptian Arab, 14.9, 2019-10-12,
INSERT INTO Horse (RegisteredName, Breed, Height, BirthDate) VALUES ('Babe', 'Quarter Horse', 15.3, '2015-02-10'), ('Independence', 'Holsteiner', 16.0, '2017-03-13'), ('Ellie', 'Saddlebred', 15.0, '2016-12-22'), (NULL, 'Egyptian Arab', 14.9, '2019-10-12');
The Movie table has the following columns: ID—integer, primary key, auto-increment, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, The following data needs to be added to the Movie table: Title Genre RatingCode Year, Pride and Prejudice Romance G 2005, Write a SQL statement to insert the indicated data into the Movie table.
INSERT INTO Movie (Title, Genre, RatingCode, Year) VALUES ('Pride and Prejudice', 'Romance', 'G', 2005);
Define a INSERT statement?
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Which of the following is a DML command?
INSERT, DELETE, UPDATE
What are some constraint facts about materialized view?
It is stored and it must be refreshed whenever the base table changes.
Assume there are two tables, A and B. Which rows will always be included in the result set if Table A is inner joined with Table B?
Only rows in Tables A and B that share the join condition
How would a database engine process an update that violates a RESTRICT referential integrity constraint?
RESTRICT CONSTRAINT will reject the UPDATE if the input does not coincide with predetermined values and generates an error.
JOIN clause to select all records from the two tables where there is a match in both tables.
SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.
SELECT * FROM Orders RIGHT JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
The database contains a table named Book. Write a SQL query to return all data from the Book table without directly referencing any column names.
SELECT * FROM Book;
Once a view is created, create a query to view it.
SELECT * FROM NamedView;
Use an SQL function to calculate the average price of all products.
SELECT AVG(Price) FROM Products;
Which query illustrates performing an outer join of the Book table with a different table?
SELECT Book.Title, A.Author FROM Book B RIGHT JOIN Author A ON B.AuthorID = A.ID
The Book table has the following columns: ID—integer, primary key, auto_increment, Title—variable-length string, Genre—variable-length string, Year—integer, Write a SQL query to return how many books have a Year value of 2019.
SELECT COUNT(*) FROM Book WHERE Year = 2019;
Use the correct function to return the number of records that have the Price value set to 18.
SELECT COUNT(*) FROM Products WHERE Price = 18;
The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, Write a SQL query to return how many movies have a Year value of 2019.
SELECT COUNT(*) AS MovieCount FROM Movie WHERE Year = 2019;
List the number of customers in each country, ordered by the country with the most customers first.
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;
The Employee table has the following columns: ID - integer, primary key, FirstName - variable-length string, LastName - variable-length string, ManagerID - integer, Write a SELECT statement to show a list of all employees' first names and their managers' first names. List only employees that have a manager. Order the results by Employee first name. Use aliases to give the result columns distinctly different names, like "Employee" and "Manager". Hint: Join the Employee table to itself using INNER JOIN.
SELECT E.FirstName AS Employee, M.FirstName AS Manager FROM Employee E JOIN Employee M ON E.ManagerID = M.ID ORDER BY E.FirstName;
The Book table has the following columns: ID—integer, primary key, auto_increment, Title—variable-length string, Genre—variable-length string, Year—integer, Write a SQL query to output the unique Genre values and the number of books with each genre value from the Book table as GenreCount. Sort the results by the Genre in alphabetical order A-Z. Ensure your result set returns the columns in the order indicated.
SELECT Genre, COUNT(*) AS GenreCount FROM Book GROUP BY Genre ORDER BY Genre ASC;
The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key, RegisteredName, Breed, Height, BirthDate, Student with columns: ID - primary key, FirstName, LastName, Street, City, State, Zip, Phone, EmailAddress, LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID), StudentID - foreign key references Student(ID), LessonDateTime - partial primary key, Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule. Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.
SELECT LS.LessonDateTime, LS.HorseID, S.FirstName, S.LastName From LessonSchedule LS Join Student S on LS.StudentID = S.ID Where LS.StudentID is not Null Order BY LS.LessonDateTime ASC, LS.HorseID ASC;
The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key, RegisteredName, Breed, Height, BirthDate, Student with columns: ID - primary key, FirstName, LastName, Street, City, State, Zip, Phone, EmailAddress, LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID), StudentID - foreign key references Student(ID), LessonDateTime - partial primary key, Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results. Hint: Perform a join on the LessonSchedule, Student, and Horse tables, matching the student IDs and horse IDs.
SELECT LS.LessonDateTime, S.FirstName, S.LastName, H.RegisteredName FROM LessonSchedule LS LEFT JOIN Student S ON LS.StudentID = S.ID LEFT JOIN Horse H ON LS.HorseID = H.ID WHERE LS.LessonDateTime >= '2020-02-01' AND LS.LessonDateTime < '2020-02-02' ORDER BY LS.LessonDateTime ASC, H.RegisteredName ASC;
Use an SQL function to select the record with the highest value of the Price column.
SELECT MAX(Price) FROM Products;
Use the MIN function to select the record with the smallest value of the Price column.
SELECT MIN(Price) FROM Products;
The Movie table has the following columns: ID - integer, primary key, Title - variable-length string, Genre - variable-length string, RatingCode - variable-length string, Year - integer, The Rating table has the following columns: Code - variable-length string, primary key, Description - variable-length string, Write a SELECT statement to select the Title, Year, and rating Description. Display all movies, whether or not a RatingCode is available. Hint: Perform a LEFT JOIN on the Movie and Rating tables, matching the RatingCode and Code columns.
SELECT Movie.Title, Movie.Year, Rating.Description FROM Movie LEFT JOIN Rating ON Movie.RatingCode = Rating.Code;
The Movie table has the following columns: ID - integer, primary key, Title - variable-length string, Genre - variable-length string, RatingCode - variable-length string, Year - integer, The YearStats table has the following columns: Year - integer, TotalGross - bigint unsigned, Releases - integer, Write a SQL query to display both the Title and the TotalGross (if available) for all movies. Ensure your result set returns the columns in the order indicated.
SELECT Movie.Title, YearStats.TotalGross FROM Movie LEFT JOIN YearStats ON Movie.Year = YearStats.Year;
The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, The YearStats table has the following columns: Year—integer, TotalGross—bigint unsigned, Releases—integer, Write a SQL query to display both the Title and the TotalGross (if available) for all movies. Ensure your result set returns the columns in the order indicated.
SELECT Movie.Title, YearStats.TotalGross FROM Movie LEFT JOIN YearStats ON Movie.Year = YearStats.Year;
JOIN clause to join the two tables Orders and Customers, using the CustomerID field in both tables as the relationship between the two tables.
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, Write a SQL query to output the unique RatingCode values and the number of movies with each rating value from the Movie table as RatingCodeCount. Sort the results by the RatingCode in alphabetical order A-Z. Ensure your result set returns the columns in the order indicated.
SELECT RatingCode, Count(*) AS RatingCodeCount FROM Movie Group BY RatingCode Order BY RatingCode ASC;
The Horse table has the following columns: ID - integer, primary key, RegisteredName - variable-length string, Breed - variable-length string, Height - decimal number, BirthDate - date, Write a SELECT statement to select the registered name and height for only horses that have an above average height. Order the results by height (ascending). Hint: Use a subquery to find the average height.
SELECT RegisteredName, Height From Horse Where Height > ( SELECT AVG(Height) From Horse ) Order By Height ASC;
Use an SQL function to calculate the sum of all the Price column values in the Products table.
SELECT SUM(Price) FROM Products;
The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, Write a SQL query to display all Title values in alphabetical order A-Z.
SELECT Title FROM Movie ORDER BY Title ASC;
The Book table has the following columns: ID—integer, primary key, auto_increment, Title—variable-length string, Genre—variable-length string, Year—integer, Write a SQL query to display all Title values in alphabetical order A-Z.
SELECT Title FROM Book ORDER BY Title ASC;
The Book table has the following columns: ID—integer, primary key, auto_increment, Title—variable-length string, Genre—variable-length string, Year—integer, Write a SQL query to retrieve the Title and Genre values for all records in the Book table with a Year value of 2020. Ensure your result set returns the columns in the order indicated.
SELECT Title, Genre FROM Book WHERE Year = 2020;
The Book table has the following columns: ID—integer, primary key, auto_increment, Title—variable-length string, Genre—variable-length string, Year—integer, The YearSales table has the following columns: Year—integer, TotalSales—bigint unsigned, Releases—integer, Write a SQL query to display both the Title and the TotalSales (if available) for all books. Ensure your result set returns the columns in the order indicated.
SELECT Title, TotalSales FROM Book LEFT JOIN YearSales ON Book.Year = YearSales.Year;
The Movie table has the following columns: ID - integer, primary key, Title - variable-length string, Genre - variable-length string, RatingCode - variable-length string, Year - integer, Write a SELECT statement to select the year and the total number of movies for that year. Hint: Use the COUNT() function and GROUP BY clause.
SELECT Year, COUNT(*) AS TotalMovies FROM Movie GROUP BY Year;
The Horse table has the following columns: ID - integer, primary key, RegisteredName - variable-length string, Breed - variable-length string, Height - decimal number, BirthDate - date, Write a SELECT statement to select the registered name, height, and birth date for only horses that have a height between 15.0 and 16.0 (inclusive) or have a birth date on or after January 1, 2020.
Select RegisteredName, Height, Birthdate From Horse WHERE (Height BETWEEN 15.0 AND 16.0) OR (Birthdate >= '2020-01-01');
The Book table has the following columns: ID—integer, primary key, auto_increment, Title—variable-length string, Genre—variable-length string, Year—integer, Write a SQL statement to update the Year value to be 2022 for all books with a Year value of 2020.
UPDATE Book SET Year = 2022 WHERE Year = 2020;
Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value "Norway".
UPDATE Customers SET City = 'Oslo' WHERE Country = 'Norway';
Update the City value and the Country value.
UPDATE Customers SET City = 'Oslo', Country = 'Norway' WHERE CustomerID = 32;
The Horse table has the following columns: ID - integer, auto increment, primary key, RegisteredName - variable-length string, Breed - variable-length string, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred, Height - decimal number, must be ≥ 10.0 and ≤ 20.0, BirthDate - date, must be ≥ Jan 1, 2015, Make the following updates: Change the height to 15.6 for horse with ID 2. Change the registered name to Lady Luck and birth date to May 1, 2015 for horse with ID 4. Change every horse breed to NULL for horses born on or after December 22, 2016.
UPDATE Horse SET Height = 15.6 WHERE ID = 2; UPDATE Horse SET RegisteredName = 'Lady Luck', BirthDate = '2015-05-01' WHERE ID = 4; UPDATE Horse SET Breed = NULL WHERE BirthDate >= '2016-12-22';
The Movie table has the following columns: ID—integer, primary key, Title—variable-length string, Genre—variable-length string, RatingCode—variable-length string, Year—integer, Write a SQL statement to update the Year value to be 2022 for all movies with a Year value of 2020.
UPDATE MOVIE SET Year = 2022 WHERE Year = 2020;
Define a UPDATE statement?
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
In Employee table, create query to update salary of all employees who make less than 70k, add 1000 to their salary.
UPDATE your_table_name SET salary = salary + 1000 WHERE salary > 70000;