Relational Database Management

Podcast Beta

Play an AI-generated podcast conversation about this lesson

Questions and Answers

Each value in a field in a table must be unique. (True or False)

False (correct)

True

You need to change the ProductCategory value for all the spoons in the Product to 43. Which statement should you use?

UPDATE Product WHERE ProductDescription = 'spoon' TO ProductCategory = 43

UPDATE Product SET ProductCategory = 43 WHERE ProductDescription = 'spoon' (correct)

UPDATE Product TO ProductCategory = 43 WHERE ProductDescription = 'spoon'

UPDATE Product WHERE ProductDescription = 'spoon' SET ProductCategory = 43

You must delete all records with the GivenName Tia from the Volunteer table. Which SQL statement should you use?

DELETE FROM Volunteer WHERE GivenName = 'Tia' (correct)

DELETE FROM Volunteer WHERE GivenName IS 'Tia'

DELETE FROM Volunteer WHERE GivenName EQUALS 'Tia'

DELETE FROM Volunteer WHERE GivenName = = 'Tia'

If deleting a row in a table automatically deletes the corresponding rows in another table, what type of delete process is this?

Cascade delete Signup and view all the answers

Match the following programming languages with their primary usage:

Python = General-purpose programming JavaScript = Client-side scripting for web applications SQL = Database queries CSS = Styling web pages Signup and view all the answers

Which modification should you make to the SQL query to avoid syntax error and return accurate results?

Change the HAVING clause to HAVING COUNT(1) = 1 Signup and view all the answers

Which statement creates an index?

CREATE TABLE Employee (EmployeeID INTEGER PRIMARY KEY) Signup and view all the answers

What is the result of executing the INSERT INTO Road VALUES (1234, 36) statement?

A new row in the table Signup and view all the answers

What is likely the cause of the null values when running the stored procedure query?

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

When searching for a product name that contains a specific character, which keyword should you use in the WHERE clause?

Like Signup and view all the answers

Which feature does a relational database use to ensure that data entered into a column is valid?

A constraint Signup and view all the answers

Which statement should you use to remove a foreign key?

Delete foreign key Signup and view all the answers

Select the two columns to create a composite primary key for the ChapterLanguage table:

ChapterID = City Region = LanguageName Country = LanguageID Signup and view all the answers

Which database object can you use to store long, complex queries that can be manually called?

Stored procedure Signup and view all the answers

Which query will increase the price of item 1 by 6 percent?

UPDATE Products SET Price = Price * 1.06 WHERE ItemNumber = 1; Signup and view all the answers

To delete a database table, which data definition language (DDL) keyword should you use?

Drop Signup and view all the answers

What type of operation is called when executing SELECT statements that refer to multiple tables without specifying join conditions?

Cartesian product Signup and view all the answers

To normalize a database to first normal form, which requirements must you meet?

Exclude repeating groups Signup and view all the answers

Match each data type with the correct column name in the school attendance data set:

StudentName = String of characters GradeLevel = Whole number DaysAbsent = Number with one decimal place Signup and view all the answers

Which statement deletes the rows where the employee’s phone number is not entered?

DELETE FROM employee WHERE phone IS NULL Signup and view all the answers

You can delete data by using a stored procedure.

False Signup and view all the answers

Which syntax should you use to create a database object named Student with specified columns?

CREATE TABLE Student(ID INT, Name VARCHAR (100), Age INT) Signup and view all the answers

Which statement should you use to disable User1’s access to view the data in the Customer table?

REVOKE SELECT ON Customer FROM User1 Signup and view all the answers

Which two SQL statements can you use to insert a record into a table with specified columns?

INSERT INTO AddressInfo VALUES ('1234 Main Street', 'Dallas', 'TX', '75201') Signup and view all the answers

How should you correct the query to return the student name and course name using both tables?

SELECT students.name, courses.name FROM students INNER JOIN courses ON students.courseID = courses.coursesID Signup and view all the answers

What is likely the cause of the error message the developer received when adding a column named Prefix in a table?

The DEFAULT keyword should be used to specify a default value. Signup and view all the answers

What is the result of executing the statement 'DELETE FROM Student'?

All rows in the table will be deleted Signup and view all the answers

Which database term is used to describe the relationship between ProductID and ProductCategory?

functionally dependent Signup and view all the answers

Which database object should you add to the Sales table to ensure each record has a valid associated salesperson record?

Foreign key Signup and view all the answers

Which statement should you use to return the number of rows in the Employee table?

SELECT COUNT(*) FROM Employee Signup and view all the answers

Which query will retrieve ItemName and Price when 'chocolate' appears in the ItemDescription column?

SELECT ItemName, Price FROM Products WHERE ItemDescription LIKE '%chocolate%'; Signup and view all the answers

Which SQL statement should you use to combine the results of two queries into a single result?

UNION Signup and view all the answers

Which statement creates a composite key?

CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY(OrderID, OrderItemID)) Signup and view all the answers

Which data type should you recommend to store charges on purchases in a database table to allow financial functions?

Decimal Signup and view all the answers

Which statement removes all rows from a table without logging the individual row deletions?

TRUNCATE TABLE Signup and view all the answers

How many rows are returned by the SQL statement 'SELECT * FROM Cars C WHERE c.Origin <> 'USA' AND c.Color <> 'Black''?

5 Signup and view all the answers

What type of operation is executed when performing 'SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department'?

Cartesian product Signup and view all the answers

Which SQL query should be used to display the flight numbers of all flights that arrive at LaGuardia Airport (LGA) later today, sorted by the most recent ____________?

ArrivalTime Signup and view all the answers

Which query returns a result set of orders placed after January 2017 in all states except California (CA)?

SELECT * FROM orders WHERE order_date > '2017-01-01' AND ship_state <> 'CA' Signup and view all the answers

How are the rows organized in the result set of the SELECT query on the Customer table?

In the order in which the rows were inserted Signup and view all the answers

Which statement should be used to remove a view named EmployeeView from the database?

DROP VIEW EmployeeView Signup and view all the answers

Which SQL statement will return the country and number of orders in each country where the number of orders is less than 50?

SELECT COUNT(OrderId), Country FROM Orders GROUP BY Country HAVING COUNT(OrderId) < 50; Signup and view all the answers

Which database term describes the relationship between productID and productcategory?

Functionally dependent Signup and view all the answers

What is the result in the database if a transaction fails after deleting only 40 rows out of 100?

40 rows will be deleted from the table Signup and view all the answers

What changes are needed in the query to remove the SSN column from the Customers table?

ALTER TABLE Customers DELETE COLUMN SSN; Signup and view all the answers

How many rows does the SQL statement return after performing a LEFT OUTER JOIN between the Cars and Colors tables?

3 Signup and view all the answers

Which two SQL keywords are valid to use in a CREATE TABLE statement?

Constraint Signup and view all the answers

Which query correctly returns a result set for all orders excluding Texas (TX) and Arizona (AZ) in the Ship_State?

SELECT * FROM Orders WHERE NOT Ship_State = 'TX' AND NOT Ship_State = 'AZ'; Signup and view all the answers

A full database backup is a copy of all the data in the entire database

True Signup and view all the answers

A file or filegroup restore specifies a portion of the database to recover

True Signup and view all the answers

To create a view that returns an alphabetical list of game names from the Games table, which query should be used?

CREATE VIEW Mygames As SELECT Name From Games ORDER BY Name Signup and view all the answers

One reason to create a stored procedure is to:

Give the user control of the query logic Signup and view all the answers

To ensure that an employee can be assigned to only an existing department, what should be applied to the Employee table?

A foreign key Signup and view all the answers

Which statement should be used to populate a table named employeecopy with data from an existing table named employee?

Select * Into Employeecopy Signup and view all the answers

Which query should be used to display various calculations related to orders in the ItemsOnOrder table?

SELECT COUNT(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder HAVING ItemNumber, Quantity, UnitPrice Signup and view all the answers

Which statement should be used to delete a record from the Customer table with a CustomerID of 12345?

DELETE FROM Customer WHERE CustomerID = 12345 Signup and view all the answers

What is the result of the statement inserting data into the Product table where the CategoryID is a foreign key?

A foreign key constraint violation Signup and view all the answers

The process of automatically deleting corresponding rows in another table when rows are deleted in the main table is an example of:

Cascade delete Signup and view all the answers

Which keyword combines the results of two queries and returns only rows that appear in both result sets?

INTERSECT Signup and view all the answers

Which statement correctly updates the 'Books' column for Harry in San Francisco in the LoanedBooks table?

UPDATE LoanedBooks SET Books = 0 WHERE (Name = 'Harry' OR City = 'San Fransisco') Signup and view all the answers

After executing a statement to delete all products in the Furniture category, what is the result set of the view that included only products from that category?

Unchanged Signup and view all the answers

How many rows will be included in the Cartesian product of two tables in a cross join without a WHERE clause?

6 Signup and view all the answers

To create a new column named District in the Customer table, which ALTER TABLE statement should be used?

ALTER TABLE Customer ADD (District INTEGER) Signup and view all the answers

One difference between a function and a stored procedure is that a function:

Must return a value Signup and view all the answers

Which SQL statement is considered a data manipulation language (DML) statement?

SELECT EmployeeName FROM Employee WHERE EmployeeName = 'Jack Smith'; Signup and view all the answers

Study Notes

Database Fundamentals

A relational database uses constraints to ensure data entered into a column is valid.
A primary key is a column or set of columns that uniquely identifies each row in a table.
A foreign key is a column or set of columns in a table that references the primary key of another table.

SQL Queries

To update a value in a table, use the UPDATE statement followed by the table name, SET clause, and WHERE clause.
To delete a row from a table, use the DELETE statement followed by the table name and WHERE clause.
To create a query that returns a result set for all orders where the ship_state excludes certain states, use the NOT keyword with the AND or OR operator.
To create a view that returns an alphabetical list of game names, use the CREATE VIEW statement with the SELECT and ORDER BY clauses.

Database Design

When designing a table, each value in a field must be unique, each row in a table must be unique, and each column name in a table must be unique.
To ensure data consistency, use a primary key, foreign key, and constraints.
To create a relationship between two tables, use a foreign key in one table that references the primary key of another table.

Indexing and Performance

An index can improve query performance by allowing the database to quickly locate specific data.
A clustered index rearranges the physical order of the rows in a table, while a non-clustered index creates a separate data structure that contains the index keys and pointers to the rows.
To improve the performance of a query, consider creating an index on columns used in the WHERE clause.

Data Types

When defining a column in a table, choose a data type that is appropriate for the type of data being stored.
Common data types include INT, VARCHAR, CHAR, DATETIME, and NULL.
Consider the maximum length of a string when choosing a data type for a column.

Transactions and Backup

A transaction is a sequence of operations that are executed as a single, all-or-nothing unit.
If a transaction fails, the database is rolled back to a previous state.
A full database backup is a copy of all the data in the entire database.
A differential backup copies only the data that has changed since the last full backup.

Stored Procedures

A stored procedure is a reusable sequence of SQL statements that can be executed with a single command.
Stored procedures can improve performance and reduce the amount of network traffic.
One reason to create a stored procedure is to improve performance.

Views

A view is a virtual table that is based on the result of a query.
Views can be used to simplify complex queries, improve security, and reduce the amount of data that is returned.
To create a view, use the CREATE VIEW statement with the SELECT clause.

Normalization

Normalization is the process of organizing the data in a database to minimize data redundancy and improve data integrity.
To normalize a database to first normal form, exclude repeating groups and ensure that each column contains only atomic values.
To normalize a database to higher normal forms, eliminate partial dependencies and transitive dependencies.

Joins

A join is a operation that combines rows from two or more tables.
There are several types of joins, including INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN.
Joins can be used to combine data from multiple tables into a single result set.### Database Management

UPDATE Statement

The UPDATE statement is used to modify existing data in a table.
Example: UPDATE CustomerID FROM Customer DELETE * WHERE CustomerID = 12345

INSERT Statement

The INSERT statement is used to add new data to a table.
Example: INSERT INTO Product VALUES (3296, ‘Table’, 4444)

Foreign Key Constraint

A foreign key constraint ensures that the value in a column matches the value in another table.
Example: CategoryID in the Product table is a foreign key referencing the Category table.

Cascade Delete

A cascade delete is a process where deleting a row in a table automatically deletes related rows in another table.
Example: deleting a row in the Order table automatically deletes related rows in the OrderItem table.

Intersecting Queries

The INTERSECT keyword is used to combine the results of two queries and return only rows that appear in both result sets.
Example: SELECT * FROM table1 INTERSECT SELECT * FROM table2

Updating Table Data

The UPDATE statement is used to modify existing data in a table.
Example: UPDATE LoanedBooks SET Books = 0 WHERE (Name = ‘Harry’ AND City = ‘San Fransisco’)

View Creation

A view is a virtual table based on the result of a query.
Example: creating a view that includes all products from the Product table that are in the Furniture category.

Cross Join

A cross join is a type of join that returns the Cartesian product of two tables.
Example: SELECT * FROM table1 CROSS JOIN table2

Altering Table Structure

The ALTER TABLE statement is used to modify the structure of a table.
Example: ALTER TABLE Customer ADD (District INTEGER)

Stored Procedures

A stored procedure is a set of SQL statements that can be executed with a single command.
Example: creating a stored procedure to delete data from a table.

Normalization

Normalization is the process of organizing data in a database to minimize data redundancy and improve data integrity.
Example: identifying the column that prevents a table from being in third normal form.

Granting and Revoking Access

The GRANT and REVOKE statements are used to grant and revoke access to a table.
Example: REVOKE SELECT ON Customer FROM User1

Inserting Data

The INSERT statement is used to add new data to a table.
Example: INSERT INTO AddressInfo VALUES (‘1234 Main Street’, ‘Dallas’, ‘TX’, ‘75201’)

Joining Tables

A join is used to combine data from two or more tables.
Example: SELECT students.name, courses.name FROM students INNER JOIN courses ON students.courseID = courses.courseID

Database Objects

A database object is a component of a database, such as a table, view, or stored procedure.
Example: creating a database object to store student information.

Constraints

A constraint is a rule that ensures the integrity of data in a table.
Example: adding a constraint to a table to ensure that a column is not null.

Deleting Data

The DELETE statement is used to delete data from a table.
Example: DELETE FROM Student

Database Relationships

A database relationship is a connection between two or more tables.
Example: identifying the relationship between the ProductID and ProductCategory columns.

Indexing

An index is a data structure that improves the speed of data retrieval.
Example: creating a clustered index on a table.

Running Financial Functions

A data type is a type of data that can be stored in a column.
Example: recommending a data type to store financial data in a table.

Logging Row Deletions

The TRUNCATE TABLE statement is used to delete all rows from a table without logging the individual row deletions.
Example: TRUNCATE TABLE Cars

Query Operations

A query operation is a type of operation that retrieves data from a table.
Example: identifying the type of operation that retrieves data from two tables.

Joining Tables with Conditions

A join with conditions is used to combine data from two or more tables based on a condition.
Example: SELECT * FROM Cars C WHERE c.Origin <> ‘USA’ AND c.Color <> ‘Black’

Data Modeling

Data modeling is the process of creating a conceptual representation of data.
Example: recommending a data type to store financial data in a table.

Querying Data

A query is a request to retrieve data from a table.
Example: SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department

Data Types

A data type is a type of data that can be stored in a column.
Example: identifying the data type of a column.

Filtering Data

A filter is a condition that is used to select specific data from a table.
Example: SELECT * FROM Flight WHERE DestinationAirport = ‘LGA’ AND ArrivalTime > SYSDATE

Sorting Data

A sort is a operation that arranges data in a specific order.
Example: SELECT * FROM Flight WHERE DestinationAirport = ‘LGA’ AND ArrivalTime > SYSDATE ORDER BY ArrivalTime DESC

Querying with Multiple Conditions

A query with multiple conditions is used to retrieve data from a table based on multiple conditions.
Example: SELECT * FROM orders WHERE order_date > ‘2017-01-01’ AND ship_state <> ‘CA’

Studying That Suits You

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

Description

Test your knowledge of relational database management concepts, including table structures and data integrity.