Creating Views in SQL

22 Questions

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

REVOKE

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)

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 = ‘LGA’ AND ArrivalTime > GETDATE() ORDER BY ArrivalTime

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

Non-clustered index

What is the purpose of the REVOKE command?

To revoke privileges from a user

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

Improves the performance of queries on columns that are accessed randomly

What is the purpose of the CREATE TABLE command?

To create a new table

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

IMAGE

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

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

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

The Prefix or FirstName columns have null values

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

To ensure data integrity by preventing duplicate values

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

It allows for faster query execution times

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

Modular code reuse and reduced network traffic

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

To filter the results based on specific conditions

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

The view result set is automatically updated

What is the purpose of the INTERSECT keyword in SQL?

To return only rows that appear in both result sets

What is a benefit of using stored procedures?

Improved performance

What is the purpose of normalizing a database?

To eliminate data redundancy

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

Full outer join

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

To define the view columns

What is the purpose of the UPDATE VIEW statement?

To modify an existing view

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.

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.

Make Your Own Quizzes and Flashcards

Convert your notes into interactive study material.