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