Database Relationships and Operations Quiz
40 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What should the WHERE clause contain to ensure only non-null phone numbers are included in the result set?

  • PhoneNumber IS NOT NULL (correct)
  • PhoneNumber != NULL
  • PhoneNumber IS NULL
  • PhoneNumber EXISTS

    • Which option correctly completes the ORDER BY clause to sort by LastName?

  • ORDER BY LastName DESC
  • ORDER BY LastName
  • ORDER BY LastName WITHIN GROUP
  • ORDER BY LastName ASC (correct)

    • Which query correctly retrieves the total number of orders and aggregates the line item totals?

  • SELECT COUNT(ID), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder GROUP BY ItemNumber
  • SELECT COUNT(ID), AVG(LineItemTotal), MAX(LineItemTotal), SUM(LineItemTotal) FROM ItemsOnOrder (correct)
  • SELECT SUM(ID), COUNT(LineItemTotal), MIN(LineItemTotal), MAX(ID) FROM ItemsOnOrder
  • SELECT COUNT(LineItemTotal), AVG(ID), MAX(Quantity), SUM(UnitPrice) FROM ItemsOnOrder

    • Which SQL statement is correct for updating the ProductCategory of a product with the description 'spoon'?

    <p>UPDATE Product SET ProductCategory = 43 WHERE ProductDescription = 'spoon'</p> Signup and view all the answers

    Which statement is suitable for populating the EmployeeCopy table with data from the Employee table?

    <p>INSERT INTO EmployeeCopy SELECT * FROM Employee</p> Signup and view all the answers

    What does the HAVING clause do in SQL queries?

    <p>Filters records after grouping</p> Signup and view all the answers

    What term best describes the relationship between ProductID and ProductCategory in a table?

    <p>relationally dependent</p> Signup and view all the answers

    Which of the following queries will result in a syntax error due to improper SQL structure?

    <p>SELECT AVG(LineItemTotal) WHERE ItemNumber = 100</p> Signup and view all the answers

    What type of SQL operation is executed with the query: SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department?

    <p>cartesian product</p> Signup and view all the answers

    What will be the output of executing a query with GROUP BY without any aggregate function?

    <p>Rows grouped but no aggregation</p> Signup and view all the answers

    Which SQL statement successfully creates an index?

    <p>CREATE TABLE Employee (EmployeeID INTEGER PRIMARY KEY)</p> Signup and view all the answers

    Which two keywords are valid in a CREATE TABLE statement?

    <p>CONSTRAINT</p> Signup and view all the answers

    How do you calculate the average line item total in SQL?

    <p>Using AVG(LineItemTotal)</p> Signup and view all the answers

    What SQL statement should be used to delete all records with the GivenName 'Tia' from the Volunteer table?

    <p>DELETE FROM Volunteer WHERE GivenName = 'Tia'</p> Signup and view all the answers

    In the result set of this query: SELECT CustomerID, FirstName, DateJoined FROM Customer, how are the rows organized?

    <p>Randomly, as there's no specified order</p> Signup and view all the answers

    Which SQL statement correctly modifies the data of a table called 'Products'?

    <p>UPDATE Products SET Item = 'Table' WHERE ID = 1</p> Signup and view all the answers

    What is the likely cause of the error message when trying to add the Prefix column to the Person table?

    <p>The varchar(4) data type is invalid for the Person table</p> Signup and view all the answers

    Which SQL query correctly selects all students who either enrolled after June 1, 2020, or graduated in 2022?

    <p>SELECT * FROM students WHERE enrollment_date &gt;= ‘2020-06-01’ OR academic_status = ‘Graduated’ OR graduation_date &gt;= ‘2020-01-01’ ORDER BY enrollment_date DESC</p> Signup and view all the answers

    How many rows are returned by the SQL statement selecting cars with Origin 'USA' and Color 'Black'?

    <p>5</p> Signup and view all the answers

    What SQL keyword is necessary to specify that a column should not accept null values when adding it to a table?

    <p>NOT NULL</p> Signup and view all the answers

    Which of the following statements would modify an existing column to allow NULL values in SQL?

    <p>ALTER TABLE Person MODIFY COLUMN Prefix varchar(4) NULL;</p> Signup and view all the answers

    Which condition in an SQL WHERE clause will only select records where both conditions are TRUE?

    <p>AND</p> Signup and view all the answers

    In the context of the SQL query provided, what does 'academic_status = Graduated' indicate?

    <p>Students who have successfully completed their grades</p> Signup and view all the answers

    What is the purpose of the ORDER BY clause in an SQL query?

    <p>To sort the result set based on one or more columns</p> Signup and view all the answers

    Which SQL statement correctly deletes the record with a CustomerID of 12345?

    <p>DELETE FROM Customer WHERE CustomerID = 12345</p> Signup and view all the answers

    Which of the following options is a data manipulation language (DML) statement?

    <p>INSERT INTO Employee VALUES (‘Jack Smith’);</p> Signup and view all the answers

    Which SQL query retrieves ItemName and Price where 'chocolate' appears in the ItemDescription column?

    <p>SELECT ItemName, Price FROM Products WHERE ItemDescription LIKE ‘%chocolate%;</p> Signup and view all the answers

    Which statement is true about SQL DELETE statements?

    <p>DELETE statements permanently remove data without recovery options.</p> Signup and view all the answers

    What is the purpose of the SQL SELECT statement?

    <p>To retrieve data from one or more tables.</p> Signup and view all the answers

    How does the 'IN' clause function in SQL?

    <p>It checks if a value matches any value in a list.</p> Signup and view all the answers

    What is an example of a composite key in a database?

    <p>A primary key composed of multiple columns.</p> Signup and view all the answers

    When is the 'ALTER TABLE' command used in SQL?

    <p>To modify the structure of an existing table.</p> Signup and view all the answers

    Which query will correctly create a view that returns an alphabetical list of game names?

    <p>CREATE VIEW MyGame AS SELECT Name FROM Games ORDER BY Name</p> Signup and view all the answers

    What statement should be used to revoke User1’s access to view data in the Customer table?

    <p>REVOKE SELECT ON Customer FROM User1</p> Signup and view all the answers

    Which syntax is correct for creating a Student database object with specified fields?

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

    What SQL query should be used to get the flight numbers of flights arriving at LGA later today, sorted by recent arrival time?

    <p>SELECT FlightNumber FROM Flight WHERE DestinationAirport = ‘LGA’ AND ArrivalTime &gt; GETDATE() ORDER BY ArrivalTime DESC</p> Signup and view all the answers

    What should you do to ensure that a newly created table has a clustered index?

    <p>Clustered indexes are automatically created with primary keys.</p> Signup and view all the answers

    In order to sort flight data by arrival time after filtering for LGA, which clause is needed?

    <p>ORDER BY ArrivalTime</p> Signup and view all the answers

    What would happen if you use the syntax 'CREATE TABLE Student ( ID, Name, Age )' without data types?

    <p>An error is thrown due to missing data types.</p> Signup and view all the answers

    Which of the following options correctly represents a condition to filter game names starting with 'A' to 'I'?

    <p>WHERE Name BETWEEN 'A' AND 'I'</p> Signup and view all the answers

    Study Notes

    Database Relationships

    • The relationship between ProductID and ProductCategory is functionally dependent.

    Database Operations

    • The query SELECT EmployeeID, FirstName, DepartmentName FROM Employee, Department performs a cartesian product, which combines all rows from both tables resulting in a larger result set.

    Creating Tables and Indexes

    • The statement CREATE TABLE Employee (EmployeeID INTEGER PRIMARY KEY) creates a table named Employee with a column named EmployeeID, making it the primary key.
    • The statement CREATE TABLE Employee (EmployeeID INTEGER INDEX) creates a table named Employee with a column named EmployeeID and creates an index for this column. This improves query performance by allowing faster data retrieval.

    CREATE TABLE Statements

    • PRIMARY KEY specifies the primary key column(s) within a CREATE TABLE statement.
    • CONSTRAINT defines a rule that restricts data changes in a database.

    Deleting Records

    • The SQL statement DELETE FROM Volunteer WHERE GivenName = ‘Tia’ deletes all records from the Volunteer table where the GivenName column is ‘Tia’.

    Row Organization

    • When executing the query SELECT CustomerID, FirstName, DateJoined FROM Customer, the rows in the result set are organized in an unpredictable order:
      • Unless ORDER BY is used, the order is typically determined by how the rows were inserted into the table.
    • Additional Considerations:
      • If there is a clustered index, the rows might be ordered by the clustered index column(s).
      • If there's a secondary index, the rows might be ordered by the secondary index column(s) when using that index for retrieval.

    ALTER TABLE

    • The error message received when adding a column using ALTER TABLE Person ADD Prefix varchar(4) NOT NULL is likely due to a violation of a constraint. This constraint could be a FOREIGN KEY, UNIQUE KEY, or PRIMARY KEY constraint.
    • The error is not related to data type, empty table, or the requirement for a default value.

    Selecting Data with Conditions

    • The query SELECT * FROM students WHERE enrollment_date >= ‘2020-06-01’ OR academic_status = ‘Graduated’ OR graduation_date >= ‘2020-01-01’ ORDER BY enrollment_date DESC returns all students who enrolled on or after June 1, 2020 or graduated in 2022 or both. The results are sorted in descending order of their enrollment date.

    Selecting Data with AND and OR Conditions

    • The SQL statement SELECT * FROM Cars c WHERE c.Origin = ‘USA’ AND c.Color = ‘Black’ returns 0 rows because there is no car in the table that is both from the USA and Black.

    Deleting Records

    • DELETE FROM Customer WHERE CustomerID = 12345 is used to delete the record from the Customer table with a CustomerID of 12345.

    Data Manipulation Language (DML)

    • INSERT INTO Employee VALUES (‘Jack Smith’); is a DML statement as it modifies the data within the Employee table.

    Retrieving Based on String Conditions

    • SELECT ItemName, Price FROM Products WHERE ItemDescription LIKE ‘%chocolate%’; retrieves the ItemName and Price for items whose ItemDescription column contains "chocolate".

    Composite Keys

    • A composite key is created using two or more columns as a primary key to uniquely identify rows in a database table.

    Creating a View

    • CREATE VIEW MyGame AS SELECT name FROM Games ORDER BY Name creates a view named 'MyGame' that displays the game names in alphabetical order.

    Disabling Access to a Table

    • REVOKE SELECT ON Customer FROM User1 disables User1's access to view data in the Customer table.

    Creating a Table

    • CREATE TABLE Student (ID INT, Name VARCHAR (100), Age INT) creates a table named Student with columns ID, Name, and Age of the specified data types.

    Querying Specific Data

    • SELECT FlightNumber FROM Flight WHERE DestinationAirport = ‘LGA’ AND ArrivalTime > GETDATE() ORDER BY ArrivalTime DESC retrieves all flight numbers arriving at LaGuardia Airport (LGA) later today and sorts them by the most recent arrival time.

    Clustered Index

    • A clustered index is a special kind of index that dictates the physical order of data in a table. It acts like a table of contents, organizing rows according to the indexed column(s).
    • It is important to note that a table can have only one clustered index.

    Studying That Suits You

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

    Quiz Team

    Related Documents

    ITS Database reviewer.pdf

    Description

    Test your knowledge on database relationships, operations, and table creation. This quiz covers concepts like functional dependency, cartesian products, primary keys, and indexes. Perfect for students looking to enhance their understanding of database management systems.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser