SQL Queries for Filtering States

24 Questions

What is the purpose of the query in option A?

To retrieve a specific employee's name

What is the purpose of the query in option C?

To insert a new employee into the database

What is the purpose of the query in option D?

To add a new column to the Employee table

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

SELECT ItemName, Price FROM Products WHERE ItemDescription LIKE '%chocolate%';

What is the purpose of creating a composite key?

To improve data consistency across multiple tables

What type of database constraint ensures that a column does not contain null values?

NOT NULL

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

To uniquely identify a single record in a table

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

To establish a relationship between multiple tables

What is the correct SQL query to select all orders from the Orders table where the ship state is not 'TX' or 'AZ'?

SELECT * FROM Orders WHERE NOT ship_state = 'TX' OR NOT ship_state = 'AZ'

What is the correct syntax to remove a column from a table in SQL?

ALTER TABLE Customers DROP COLUMN SSN;

What is the correct order of clauses in a SQL query to determine whether a value appears only once in a table?

SELECT, FROM, WHERE, GROUP BY, HAVING

What is the correct statement to remove a view from a database?

DROP VIEW EmployeeView

What should you apply to the Employee table to ensure that an employee can be assigned to only an existing department?

A foreign key

What is the correct statement to remove a foreign key from a table?

ALTER TABLE

What is the purpose of a foreign key in a relational database?

To establish a relationship between two tables

What is the correct syntax to alter a table to add a new column in SQL?

ALTER TABLE Customers ADD COLUMN SSN;

What is a primary key constraint in a relational database?

A constraint that ensures each row in a table is unique

What is the correct syntax to create a composite primary key in SQL?

CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY (OrderID, OrderItemID))

Which data type is suitable for storing financial data in a database table?

Decimal

What is the purpose of the UPDATE statement in SQL?

To update existing records in a table

How do you specify a condition in an UPDATE statement to update only specific records?

Using the WHERE clause

What is the purpose of the PRIMARY KEY constraint in a relational database?

To ensure data integrity by preventing duplicate values

Which SQL statement is used to count the number of rows in a table?

SELECT COUNT(*)

What is the purpose of the UNIQUE constraint in a relational database?

To ensure each value in a column is unique

Study Notes

SQL Queries

SELECT statement can be used to retrieve data from a table, with NOT operator to exclude certain values, e.g., SELECT * FROM Orders WHERE NOT ship_state = 'TX' AND NOT ship_state = 'AZ'.
Using OR instead of AND in the query can alter the result, including more records, e.g., SELECT * FROM Orders WHERE NOT ship_state = 'TX' OR NOT ship_state = 'AZ'.

Database Modifications

To remove a column from a table, use ALTER TABLE Customers DROP COLUMN SSN;.
To drop a view, use DROP VIEW EmployeeView;.
To remove a foreign key, use ALTER TABLE ... DROP FOREIGN KEY ...;.

Query Errors

A query with a syntax error may be due to incorrect ordering of clauses, e.g., SELECT Title FROM Movie WHERE Title = 'Sample Movie' ORDER BY Title GROUP BY Title HAVING COUNT(*) = 1.
Correcting the query by removing the ORDER BY clause can resolve the error.

Table Structure

Each row in a table must be unique.
Each column name in a table must be unique.
A value in a field in a table does not have to be unique.

Data Types

For financial calculations, a decimal data type is recommended, e.g., for storing charge amounts.

Updating Tables

To update a table, use UPDATE LoanedBooks SET Books = 0 WHERE (NAME = 'Harry' AND City = 'San Francisco');.
Use specific conditions to update the correct record.

Retrieving Data

To return the number of rows in a table, use SELECT COUNT(*) FROM Employee;.
To retrieve specific data, use SELECT ItemName, Price FROM Products WHERE ItemDescription LIKE '%chocolate%';.

Composite Key

A composite key is created when a primary key consists of multiple columns, e.g., CREATE TABLE Order (OrderID INTEGER, OrderItemID INTEGER, PRIMARY KEY (OrderID, OrderItemID));.

This quiz tests your understanding of SQL queries, specifically in filtering data based on state information. Identify the correct SQL query to retrieve data excluding certain states.

Make Your Own Quizzes and Flashcards

Convert your notes into interactive study material.