Download SQL Programming Exercises: Database Design and Queries and more Exams Advanced Education in PDF only on Docsity!
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
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,
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 );
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:
('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
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,
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:
