Download Data Management - Applications D427 - MYSQL - SQL Programing - Interm ediate Level questio and more Exams Advanced Education in PDF only on Docsity!
Data Management - Applications D427 - MYSQL -
SQL Programing - Interm ediate Level questions
- 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,
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.: IN- SERT 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.: SE- LECT 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
- 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,
- 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.
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.: SE- LECT 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.De- scription 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;
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, match- ing 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 (as- cending). 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,
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 Stu- dent(ID), LessonDateTime - date/time, not NULL, partial primary key,
