Creating Views in SQL

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

Which SQL command is used to revoke privileges from a user?

GRANT

REVOKE (correct)

REMOVE

DENY

What is the correct syntax to create a table named Student with columns ID, Name, and Age?

CREATE (TABLE Student ID INT, Name VARCHAR (100), Age INT)

CREATE TABLE Student (ID INT, Name VARCHAR (100), Age INT) (correct)

CREATE TABLE (ID INT, Name VARCHAR (100), Age INT)

CREATE Student (ID INT, Name VARCHAR (100), Age INT)

Which SQL query would you use to display the flight numbers of all flights that arrive at LaGuardia Airport later today?

SELECT FlightNumber FROM Flight WHERE DestinationAirport = ‘JFK’ AND ArrivalTime > GETDATE() ORDER BY ArrivalTime

SELECT FlightNumber FROM Flight WHERE DestinationAirport = ‘EWR’ AND ArrivalTime < GETDATE() ORDER BY ArrivalTime

SELECT FlightNumber FROM Flight WHERE DestinationAirport = ‘LGA’ AND ArrivalTime < GETDATE() ORDER BY ArrivalTime

SELECT FlightNumber FROM Flight WHERE DestinationAirport = ‘LGA’ AND ArrivalTime > GETDATE() ORDER BY ArrivalTime (correct)

What type of index is used to improve the performance of queries that return large result sets?

Non-clustered index Signup and view all the answers

What is the purpose of the REVOKE command?

To revoke privileges from a user Signup and view all the answers

Which of the following is a characteristic of a clustered index?

Improves the performance of queries on columns that are accessed randomly Signup and view all the answers

What is the purpose of the CREATE TABLE command?

To create a new table Signup and view all the answers

Which of the following is not a data type in SQL?

IMAGE Signup and view all the answers

What type of index would improve the efficiency of a query that searches for a specific category of products?

Non-clustered index on the Category column Signup and view all the answers

Which SQL keyword is used to search for a specific pattern in a string?

LIKE Signup and view all the answers

What is the likely cause of a stored procedure returning all null values?

The Prefix or FirstName columns have null values Signup and view all the answers

What is the purpose of a primary key in a database table?

To ensure data integrity by preventing duplicate values Signup and view all the answers

What is the benefit of creating an index on a frequently queried column?

It allows for faster query execution times Signup and view all the answers

What is the primary advantage of using a stored procedure in a database?

Modular code reuse and reduced network traffic Signup and view all the answers

What is the purpose of the WHERE clause in the NorthAmericanMammals_View?

To filter the results based on specific conditions Signup and view all the answers

What happens to the result set of a view when the underlying table data is modified?

The view result set is automatically updated Signup and view all the answers

What is the purpose of the INTERSECT keyword in SQL?

To return only rows that appear in both result sets Signup and view all the answers

What is a benefit of using stored procedures?

Improved performance Signup and view all the answers

What is the purpose of normalizing a database?

To eliminate data redundancy Signup and view all the answers

What type of join is used to retrieve all customers and their orders, as well as customers who have no orders?

Full outer join Signup and view all the answers

What is the purpose of the AS keyword in a CREATE VIEW statement?

To define the view columns Signup and view all the answers

What is the purpose of the UPDATE VIEW statement?

To modify an existing view Signup and view all the answers

Study Notes

SQL Syntax

To remove a user from a database object, use the REVOKE or REMOVE command, followed by the user name and the object name (e.g. REVOKE User1 FROM Customer or C.REMOVE User1 FROM Customer).
To create a database object, use the CREATE command followed by the object type (e.g. TABLE), the object name, and the column definitions (e.g. CREATE TABLE Student (ID INT, Name VARCHAR(100), Age INT)).

Querying

To filter data based on a specific condition, use the WHERE clause (e.g. SELECT FlightNumber FROM Flight WHERE DestinationAirport = ‘LGA’ AND ArrivalTime > GETDATE()).
To sort data in descending order, use the ORDER BY clause with the DESC keyword (e.g. ORDER BY ArrivalTime DESC).

Indexing

A clustered index improves the performance of queries that retrieve large result sets.
A clustered index does not improve the performance of queries that access columns randomly.
A primary key or unique constraint can be used in conjunction with a clustered index.

Query Optimization

To optimize a query that searches for data based on a specific category, create a non-clustered index on the category column (e.g. CREATE INDEX ON Product (Category)).
A clustered index on the ProductName column would not improve the performance of this query.
A non-clustered index on the Price column would not improve the performance of this query.

Query Keywords

To search for data that contains a specific character or pattern, use the LIKE keyword in the WHERE clause (e.g. SELECT * FROM Product WHERE ProductName LIKE '%Science%').
The BETWEEN keyword is used to filter data within a specific range.
The IN keyword is used to filter data based on a list of values.

Stored Procedures

A stored procedure can return null values if the columns used in the query contain null values.
The + operator can be used to concatenate character data.
The JOIN keyword is not required in the SELECT statement.

Views

A view is a virtual table based on the result of a query.
If the underlying data is deleted, the view will return an empty result set.
A view can be created using the CREATE VIEW command, followed by the view name and the query definition (e.g. CREATE VIEW [dbo].[NorthAmericanMammals_View] AS SELECT a.Id, a.Name FROM Animal a WHERE a.Class = ‘Mammals’ AND a.InNorthAmerica = 1).

Normalization

To normalize a database to first normal form, exclude repeating groups and avoid duplicate rows.
Composite keys and foreign keys are not excluded in first normal form.

Joins

A full join returns all rows from both tables, including those that do not have matching values.
An inner join returns only the rows that have matching values in both tables.
A full join is used to retrieve all customers and their orders, including customers who have no orders.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Learn how to create views in SQL that filter specific data from tables, such as selecting products from a certain category or mammals from a specific region.