Data Management - Applications D427 - MYSQL - SQL Programing - Intermediate Level

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

);

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)

);

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);

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;

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,

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, 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);

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);

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;

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 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;

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;

San Francisco, CA 94110 USA ,

How many attributes are present in the address fragment?

4 Attributes

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

Which data type will store "2022-01-10 14:22:12" as a temporal value without loss of information?

DATETIME

Which SQL command is an example of data definition language (DDL)?

CREATE, ALTER, DROP, RENAME, TRUNICATE

What does the SQL keyword command TRUNCATE do?

TRUNCATE removes all rows, unlike DELETE, which removes specific rows.

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.

Which restrictions applies when using a materialized view?

Materialized views require to be refreshed frequently and require more storage space.

Define a SELECT statement?

SELECT column1, column2, ... FROM table_name;

Define a INSERT statement?

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Define a UPDATE statement?

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

Define a DELETE statement?

DELETE FROM table_name

WHERE condition;

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:

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

Which data type represents numbers with fractional values:

DECIMAL

Which of the following is a DML command?

INSERT, DELETE, UPDATE

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.

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 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.

What are some constraint facts about materialized view?

It is stored and it must be refreshed whenever the base table changes.

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

);

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 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,

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;

A database has a view named BookView.

Write a SQL statement to delete the view named BookView from the database.

DROP VIEW BookView;

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 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 create an index named idx_year on the Year column of the Book table

CREATE INDEX idx_year ON Book(Year);

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);

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;

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;

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

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

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;

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,

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 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 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 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;

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 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 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 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;

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;

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;

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 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 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 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');

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 );

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')

);

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

);

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)

);

A database has a view named MovieView.

Write a SQL statement to delete the view named MovieView from the database.

Drop View MovieView;

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;

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;

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 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;

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;

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;

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)

);

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;

Once a view is created, create a query to view it.

SELECT * FROM NamedView;

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;

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;

List the number of customers in each country.

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country;

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;

Use the MIN function to select the record with the smallest value of the Price column.

SELECT MIN(Price)

FROM Products;

Use an SQL function to select the record with the highest value of the Price column.

SELECT MAX(Price)

FROM Products;

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;

Use an SQL function to calculate the average price of all products.

SELECT AVG(Price)

FROM Products;

Use an SQL function to calculate the sum of all the Price column values in the Products table.

SELECT SUM(Price)

FROM Products;

Update the City column of all records in the Customers table.

UPDATE Customers

SET City = 'Oslo';

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;

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;

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');

Add a column of type DATE called Birthday.

ALTER TABLE Persons

ADD Birthday DATE;

Use the TRUNCATE statement to delete all data inside a table.

TRUNCATE TABLE Persons;

Write the correct SQL statement to delete a table called Persons.

DROP TABLE Persons;

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)

);

Write the correct SQL statement to delete a database named testDB.

DROP DATABASE testDB;

Write the correct SQL statement to create a new database called testDB.

CREATE DATABASE testDB;

Delete the column Birthday from the Persons table.

ALTER TABLE Persons

DROP Birthday;

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)

);