SQL
Example of referential integrity constraints: Example- CustomerID in Orders must have a matching value to a _______________ in the Customer
CustomerID
_____ allows you to specify multiple values in a WHERE clasue
IN operator
What is the most common type of join? INNER JOIN INSIDE JOIN JOINED TABLE JOINED
INNER JOIN
Select all records where the second letter of the City is an "a".
SELECT * FROM Customers WHERE City LIKE '__a%';
Select all records where the value of the City column starts with the letter "a".
SELECT * FROM Customers WHERE City LIKE 'a%';
specifies which columns are to be listed in the query results
SQL SELECT clause
How would you write a query to show all customer info whose customerID is between 10 and 20
Select * FROM CUSTOMER WHERE CustomerID BETWEEN 10 AND 20;
The BETWEEN operator is inclusive T/F
T
alias only exists for the duration of the query. T/F
T
Which SQL statement is used to update data in a database? SAVE MODIFY UPDATE SAVE AS
UPDATE
specifies which rows are to be listed in the query result
WHERE clause
The column_name are separated by a ____________ and the SQL statement ends with a ____________
comma semi-colon
<> means what?
not equal to
When displaying the Customers table, make an ALIAS of the PostalCode column, the column should be called Pno instead.
SELECT CustomerName, Address, PostalCode AS PNO FROM Customers;
Select all the different values from the Country column in the Customers table. ____________ ______________ Country FROM Customers;
SELECT DISTINCT
Which SQL statement is used to return only different values? SELECT DIFFERENT SELECT DISTINCT SELECT UNIQUE
SELECT DISTINCT
Which command is used to SELECT only one copy of each set of duplicable rows SELECT DISTINCT SELECT UNIQUE SELECT DIFFERENT All of the above
SELECT DISTINCT
statement used to return only distinct (different) values
SELECT DISTINCT
With SQL, how do you select a column named "FirstName" from a table named "Persons"? SELECT FirstName FROM Persons SELECT Persons.FirstName EXTRACT FirstName FROM Persons
SELECT FirstName FROM Persons
What are the 3 statements in the basic framework for the SQL query statement?
SELECT clause FROM clause WHERE clause
Write SQL to Select all records where the City column has the value 'Berlin' or 'London'.
SELECT* FROM Customers WHERE City = 'Berlin' OR City = 'London';
Write a statement that will select the City column from the Customers table. ________ __________ ___________ Customers;
Select City From
The NOT NULL constraint enforces a column to not accept NULL values. T/F
T
The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true T/F
T
The WHERE clause can be combined with AND, OR and NOT operators T/F
T
What are the SQL data definition statements
CREATE ◦ To create database objects ◦ ALTER ◦ To modify the structure and/or characteristics of database objects ◦ DROP ◦ To delete database objects ◦ TRUNCATE ◦ To delete table data while keeping structure
Which SQL statement is used to create a table in a database? CREATE DATABASE TAB CREATE TABLE CREATE DATABASE TABLE CREATE DB
CREATE TABLE
Is used to create the database schema, tables and table fields Used to add primary and foreign key constraint
DDL
What is SQL comprised of
DDL & DML
Which SQL statement is used to delete data from a database? COLLAPSE DELETE REMOVE
DELETE
t is used a lot in organizations to access and pull data and combine them together from different tables
DML
What does DDL stand for?
Data Definition Language
What does DML stand for? What are DML statements?
Data manipulation language (DML) - statements, which are used for querying, inserting, modifying, and deleting data
With SQL, how can you insert a new record into the "Persons" table? INSERT ('Jimmy', 'Jackson') INTO Persons INSERT VALUES ('Jimmy' , 'Jackson') INTO Persons INSERT INTO Persons VALUES ('Jimmy', 'Jackson')
INSERT INTO Persons VALUES ('Jimmy', 'Jackson')
The ______________operator is used in the where clause to search for specified patterns in a column
LIKE
Does numeric data neet to be in quotes?
No
Does DISTINCT pull in duplicate values?
No, only unique values
The ______ operator displays a record if any of the conditions separated by OR is true. AND OR NOT
OR
the In operator is a shorthand forhand for multiple Boolean _____ conditions
OR conditions
Insert the missing statement to get all the columns from the Customers table. _________________ * FROM Customers;
SELECT
Which SQL statement is used to extract data from a database? GET SELECT OPEN EXTRACT
SELECT
Select all records where the City column has the value 'Berlin' and the PostalCode column has the value 12209. _______ * FROM CUSTOMERS ________ City = 'Berlin' _____ ___________ = 12209;
SELECT WHERE AND PostalCode
Write a query that shows customer info for phone numbers that have 555 somewhere in between the numbers
SELECT * FROM CUSTOMER WHERE Phone LIKE '%5555';
Select all records from the Customers table, sort the result reversed alphabetically by the column City.
SELECT * FROM Customers ORDER BY City DESC;
Use the IN operator to select all the records where Country is either "Norway" or "France".
SELECT * FROM Customers WHERE Country IN ('Norway', 'France');
Write an SQL query that shows the order ID, Order Date, Customer ID, total amount where customer ID is 14,34,68,84 and total amount is greater than 500.
SELECT * FROM ORDERS WHERE CustomerID IN (14,34,68,84) AND TotalAmount >500);
Write a SQL query that shows all info in the product table for products from supplier ID 8,11,15,16,17-
SELECT * FROM PRODUCT WHERE SupplierID = 8 or SupplierID = 11 OR SupplierID= 15 or SupplierID = 16 OR SupplierID= 17;
Write a SQL query that shows all info in the product table for products from supplier ID 8,11,15,16,17- Use the IN operator
SELECT * FROM PRODUCT WHERE SupplierID IN (8,11,15,16,17);
Write a SQL query that shows all info in the product table whose supplier id is not 20, 21,22,23
SELECT * FROM PRODUCT WHERE SupplierID NOT IN (20, 21,22,23);
Write a query to show all the columns in the product table where unit price is greater than 10
SELECT * FROM PRODUCT WHERE UnitPrice > 10;
Use the BETWEEN operator to select all the records where the value of the Price column is NOT between 10 and 20.
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;
Use the BETWEEN operator to select all the records where the value of the ProductName column is alphabetically between 'Geitost' and 'Pavlova'.
SELECT * FROM Products WHERE PriceName BETWEEN 'Geitost' AND 'Pavlova';
write an SQL query that's going to show all info in the customer table for customers whose Customer ID is between 50 AND 60 and whose country is Germany.
SELECT * FROM customer WHERE CustomerID BETWEEN 50 AND 60 AND (Country = 'Germany') Order by Lastname DESC;
When displaying the Customers table, refer to the table as Consumers instead of Customers.
SELECT * FROM Customers AS Consumers;
Select all records where the value of the City column contains the letter "a".
SELECT * FROM Customers WHERE City LIKE '%a%';
Select all records where the value of the City column ends with the letter "a".
SELECT * FROM Customers WHERE City LIKE '%a';
Select all records where the value of the City column does NOT start with the letter "a".
SELECT * FROM Customers WHERE City NOT LIKE 'a%';
With SQL, how do you select all the columns from a table named "Persons"? SELECT *.Persons SELECT Persons SELECT * FROM Persons SELECT [all] FROM Persons
SELECT * FROM Persons
With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"? SELECT [all] FROM Persons WHERE FirstName ='Peter' SELECT * FROM Persons WHERE FirstName <> 'Peter' SELECT * FROM Persons WHERE FirstName = 'Peter' SELECT [all] FROM Persons WHERE FirstName LIKE 'Peter'
SELECT * FROM Persons WHERE FirstName = 'Peter'
What does the * do
wild card operator for selecting all the columns
Which SQL statement is used to insert new data in a database? ADD NEW ADD RECORD INSERT NEW INSERT INTO
INSERT INTO
the _______________ symbols represents a series of more than one character.
%
What are the two wildcards that are used with the like operator
% (use mostly) can represent 0, 1 or multiple characters ___ (underscore)
In SQL which command is used to change a table's storage characteristics ALTER TABLE MODIFY TABLE CHANGE TABLE all of the above
ALTER TABLE
The ___________ operator displays a record if all the conditions separated by AND are AND OR NOT
AND
The ________and _________ operators are used to filter records based on more than one condition
AND OR
What are some common SQL operators
AND, OR and NOT
Which keyword gives an alias name
AS
Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20. SELECT * FROM Products WHERE Price ___________;
BETWEEN 10 AND 20;
This operator selects values within a given range.
Between operator
specifies which tables are to be used in the query
FROM clause
If you have a % sign in the beginning and a % sign in the end what does that do? For example '%5555';
it's going to pull for numbers that have, begin with any character one characters, two characters, three, but it must have, 555 together somewhere. And then it can end with any other characters somewhere in-between.
what is a query
question asked of the database
Characters need to be enclosed in ___________
quotes
LIKE '542%' means what?
show all the orders that begin with 542 and have any other characters after that.
SQL is not a programming language, but rather a data _______________
sublanguage
SQL aliases are used to give a table or a column a __________________
temporary name
What does a referential integrity constraint used for?
to link (or reference) relations. This means that a foreign key in a relation must also exist in the relation in which it serves as the primary key
the ____________ symbol represents a single character
underscore
