Podcast Beta
Questions and Answers
Each value in a field in a table must be unique. (True or False)
You need to change the ProductCategory value for all the spoons in the Product to 43. Which statement should you use?
You must delete all records with the GivenName Tia from the Volunteer table. Which SQL statement should you use?
If deleting a row in a table automatically deletes the corresponding rows in another table, what type of delete process is this?
Signup and view all the answers
Match the following programming languages with their primary usage:
Signup and view all the answers
Which modification should you make to the SQL query to avoid syntax error and return accurate results?
Signup and view all the answers
Which statement creates an index?
Signup and view all the answers
What is the result of executing the INSERT INTO Road VALUES (1234, 36) statement?
Signup and view all the answers
What is likely the cause of the null values when running the stored procedure query?
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?
Signup and view all the answers
Which feature does a relational database use to ensure that data entered into a column is valid?
Signup and view all the answers
Which statement should you use to remove a foreign key?
Signup and view all the answers
Select the two columns to create a composite primary key for the ChapterLanguage table:
Signup and view all the answers
Which database object can you use to store long, complex queries that can be manually called?
Signup and view all the answers
Which query will increase the price of item 1 by 6 percent?
Signup and view all the answers
To delete a database table, which data definition language (DDL) keyword should you use?
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?
Signup and view all the answers
To normalize a database to first normal form, which requirements must you meet?
Signup and view all the answers
Match each data type with the correct column name in the school attendance data set:
Signup and view all the answers
Which statement deletes the rows where the employee’s phone number is not entered?
Signup and view all the answers
You can delete data by using a stored procedure.
Signup and view all the answers
Which syntax should you use to create a database object named Student with specified columns?
Signup and view all the answers
Which statement should you use to disable User1’s access to view the data in the Customer table?
Signup and view all the answers
Which two SQL statements can you use to insert a record into a table with specified columns?
Signup and view all the answers
How should you correct the query to return the student name and course name using both tables?
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?
Signup and view all the answers
What is the result of executing the statement 'DELETE FROM Student'?
Signup and view all the answers
Which database term is used to describe the relationship between ProductID and ProductCategory?
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?
Signup and view all the answers
Which statement should you use to return the number of rows in the Employee table?
Signup and view all the answers
Which query will retrieve ItemName and Price when 'chocolate' appears in the ItemDescription column?
Signup and view all the answers
Which SQL statement should you use to combine the results of two queries into a single result?
Signup and view all the answers
Which statement creates a composite key?
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?
Signup and view all the answers
Which statement removes all rows from a table without logging the individual row deletions?
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''?
Signup and view all the answers
What type of operation is executed when performing 'SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department'?
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 ____________?
Signup and view all the answers
Which query returns a result set of orders placed after January 2017 in all states except California (CA)?
Signup and view all the answers
How are the rows organized in the result set of the SELECT query on the Customer table?
Signup and view all the answers
Which statement should be used to remove a view named EmployeeView from the database?
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?
Signup and view all the answers
Which database term describes the relationship between productID and productcategory?
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?
Signup and view all the answers
What changes are needed in the query to remove the SSN column from the Customers table?
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?
Signup and view all the answers
Which two SQL keywords are valid to use in a CREATE TABLE statement?
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?
Signup and view all the answers
A full database backup is a copy of all the data in the entire database
Signup and view all the answers
A file or filegroup restore specifies a portion of the database to recover
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?
Signup and view all the answers
One reason to create a stored procedure is to:
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?
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?
Signup and view all the answers
Which query should be used to display various calculations related to orders in the ItemsOnOrder table?
Signup and view all the answers
Which statement should be used to delete a record from the Customer table with a CustomerID of 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?
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:
Signup and view all the answers
Which keyword combines the results of two queries and returns only rows that appear in both result sets?
Signup and view all the answers
Which statement correctly updates the 'Books' column for Harry in San Francisco in the LoanedBooks table?
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?
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?
Signup and view all the answers
To create a new column named District in the Customer table, which ALTER TABLE statement should be used?
Signup and view all the answers
One difference between a function and a stored procedure is that a function:
Signup and view all the answers
Which SQL statement is considered a data manipulation language (DML) statement?
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, andWHERE
clause. - To delete a row from a table, use the
DELETE
statement followed by the table name andWHERE
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 theAND
orOR
operator. - To create a view that returns an alphabetical list of game names, use the
CREATE VIEW
statement with theSELECT
andORDER 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
, andNULL
. - 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 theSELECT
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
, andRIGHT 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 theProduct
table is a foreign key referencing theCategory
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 theOrderItem
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 theFurniture
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
andProductCategory
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.
Related Documents
Description
Test your knowledge of relational database management concepts, including table structures and data integrity.