364 Exam 2 Review
What is the most common type of join?
INNER JOIN
Which SQL statement is used to delete data from a database?
DELETE
With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons table?
DELETE FROM Persons WHERE FirstName='Peter'
How would you write a statement to delete records in the "people" table where null is in the last name column?
DELETE FROM people WHERE last_name IS NULL;
Which keyword is used when you need to divide results in various ways?
GROUP BY
Which SQL statement is used to insert new data in a database?
INSERT INTO
What is the name of the operator used to limit responses by being less specific and matching values to the applied text?
LIKE operator
To be more specific, we need to tell the database what pieces of information we want to associate from one table to the next. To do that we need to add what field?
ON
Which SQL statement is used to extract data from a database?
SELECT
With SQL, how do you select all the columns from a table name "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 * FROM Persons WHERE FirstName='Peter'
Which SQL statement is used to return only different values?
SELECT DISTINCT
What does SQL stand for?
Structured Query Language
Data in the database can be modified using what keyword?
UPDATE
What keyword is used to modify data in the database?
UPDATE
Which SQL statement is used to update data in a database?
UPDATE
How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?
UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'
What 2 things do you need for doing basic math in SQL?
select statement and basic operation
The NOT NULL constraint enforces a column to not accept NULL values.
true
Which operator is used to search for a specified pattern in a column?
LIKE
During which President's term was Opioid addiction considered an epidemic?
President Obama
If we have a lengthy field name, or want to be more clear in our output, we can change the returned name of the field with what keyword?
AS
In a SELECT statement, where must the WHERE be placed?
After FROM
Which operator is used to select values within a range?
BETWEEN
Which SQL statement is used to create a table in a database?
CREATE TABLE
With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?
SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'
With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Petterson"?
SELECT * From Persons WHERE LastName BETWEEN 'Hansen' AND 'Petterson'
With SQL, how can you return the number of records in the "Persons" table?
SELECT COUNT(*) FROM Persons
With SQL, how do you select a column named "FirstName" from a table named "Persons"?
SELECT FirstName FROM Persons
In Philadelphia, between 2000 and 2008, what was the opioid crisis a result of?
doctors overprescribing
What is the percentage of opioid addicts that get addicted to a family member's prescription?
50%
IS NOT can be substituted with which character(s) to achieve the same result?
!=
How much money does the opioid crisis cost the US annually?
$80 billion
Tables are consists of
records and fields
What type of punctuation is used at the end of SQL line
semi colon
With SQL, how can you insert a new record into the "Persons" table?
INSERT INTO Persons VALUES ('Jimmy','Jackson')
What keyword tells the database how many responses we would like to see?
LIMIT keyword
Which SQL keyword is used to sort the result-set?
ORDER BY
With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?
SELECT * FROM Persons WHERE FirstName LIKE 'a%'
How do you receive a response from the database?
any question in single quotes will get a response
Which SQL data type is the best choice for storing images in a column?
binary
When deleting a row of data from a table, it is a good idea to add a ____. Otherwise, everything in the table will be deleted.
condition
What is it called when you ask to join tables based on some given criteria, that creates overlap between the tables where the criteria match?
cross join
Using a secondary SELECT statement is called a sub-query or a sub-select and it helps us to:
narrow down or zero in on a specific set of data to use in a larger query
What is the type of quote that may look better, but causes the SQL to have an error?
smart quote
What is not a recommended method for disposal of opioids?
trash
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.
true
If you are writing a statement that returns all the fields in a table, which wildcard would you use?
*
What keyword can you use to tell the database to interpret one data type as another?
CAST
With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?
SELECT * FROM Persons ORDER BY FirstName DESC
With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?
INSERT INTO Persons (LastName) VALUES ('Olsen')
What is the correct statement for adding the name 'Bob' into the table called "people" within the SQL?
People(first_name)Values('bob')