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')