COMMANDS

Question 1

Create a view.

Answer 1

CREATE VIEW view_name
AS SELECT column_name(s)
FROM table_name
WITH CHECK OPTION;
Example: CREATE VIEW MyMovies AS SELECT Title, Genre, Year FROM Movie;

Question 2

Delete View

Answer 2

DROP VIEW MovieView;

Question 3

Create table with Foreign Key

Answer 3

CREATE TABLE table2(
column1 datatype,
column2 datatype,
…
CONSTRAINT constraint_name
FOREIGN KEY (column2)
REFERENCES table1(column1)
);

Question 4

Creating Foreign Key on Existing Column

Answer 4

ALTER TABLE table_name2
ADD CONSTRAINT constraint_name
FOREIGN KEY(column_name2)
REFERENCES table_name1(column_name1);

Question 5

Creating Indexes on New Table

Answer 5

CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
INDEX(ID)
);

Question 6

Creating Indexes on Existing Table

Answer 6

CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);

Question 7

The MySQL INSERT Statement

Answer 7

INSERT INTO TABLE_NAME (column1, column2, column3,…columnN)
VALUES (value1, value2, value3,…valueN);

Question 8

The MySQL UPDATE Statement

Answer 8

UPDATE table_name SET field1 = new-value1, field2 = new-value2
[WHERE Clause]

Question 9

MySQL ORDER BY Clause

Answer 9

SELECT * FROM Table
ORDER BY Column;

SELECT * FROM Movie
ORDER BY Title ASC;

Question 10

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.

Answer 10

SELECT RatingCode, COUNT(*) AS RatingCodeCount
FROM Movie
GROUP BY RatingCode
ORDER BY RatingCode;

Question 11

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.

Answer 11

SELECT
Movie.Title,
YearStats.TotalGross
FROM
Movie
LEFT JOIN
YearStats
ON
Movie.Year = YearStats.Year;