Intro to Database FInal Flashcards

1
Q

Which operator is used to select values within a range?

A

BETWEEN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

With SQL, how do you select all the columns from a table named “Persons”?

SELECT [all] FROM Persons

SELECT *.Persons

SELECT Persons

SELECT * FROM Persons

A

SELECT * FROM Persons

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Which SQL statement is used to delete data from a database?

A

DELETE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Which SQL statement is used to update data in a database?

A

UPDATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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 FirstName=’Peter’, LastName=’Jackson’ FROM Persons

SELECT * FROM Persons WHERE FirstName=’Peter’ AND LastName=’Jackson’

SELECT * FROM Persons WHERE FirstName<>’Peter’ AND LastName<>’Jackson

A

SELECT * FROM Persons WHERE FirstName=’Peter’ AND LastName=’Jackson’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Which operator is used to select values within a range?

A

BETWEEN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

With SQL, how do you select all the columns from a table named “Persons”?

SELECT [all] FROM Persons

SELECT *.Persons

SELECT Persons

SELECT * FROM Persons

A

SELECT * FROM Persons

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Which SQL statement is used to delete data from a database?

A

DELETE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which SQL statement is used to update data in a database?

A

UPDATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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 FirstName=’Peter’, LastName=’Jackson’ FROM Persons

SELECT * FROM Persons WHERE FirstName=’Peter’ AND LastName=’Jackson’

SELECT * FROM Persons WHERE FirstName<>’Peter’ AND LastName<>’Jackson

A

SELECT * FROM Persons WHERE FirstName=’Peter’ AND LastName=’Jackson’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Which SQL statement is used to create a database table called ‘Customers’?

CREATE DATABASE TABLE Customers

CREATE DB Customers

CREATE DATABASE TAB Customers
CREATE TABLE Customers

A

CREATE TABLE Customers

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

How can you change “Hansen” into “Nilsen” in the “LastName” column in the Persons table?

MODIFY Persons SET LastName=’Hansen’ INTO LastName=’Nilsen

MODIFY Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’

UPDATE Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’

UPDATE Persons SET LastName=’Hansen’ INTO LastName=’Nilsen’

A

UPDATE Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Which SQL statement is used to extract data from a database?

GET

OPEN

SELECT

A

SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

With SQL, how do you select all the records from a table named “Persons” where the value of the column “FirstName” is “Peter”?
Correct answer:

SELECT * FROM Persons WHERE FirstName=’Peter’

SELECT [all] FROM Persons WHERE FirstName=’Peter’

SELECT * FROM Persons WHERE FirstName<>’Peter’

SELECT [all] FROM Persons WHERE FirstName LIKE ‘Peter’

A

SELECT * FROM Persons WHERE FirstName=’Peter’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

With SQL, how can you insert “Olsen” as the “LastName” in the “Persons” table?

INSERT INTO Persons (‘Olsen’) INTO LastName

INSERT INTO Persons (LastName) VALUES (‘Olsen’)

INSERT (‘Olsen’) INTO Persons (LastName)

A

INSERT INTO Persons (LastName) VALUES (‘Olsen’)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

With SQL, how can you delete the records where the “FirstName” is “Peter” in the Persons Table?

DELETE ROW FirstName=’Peter’ FROM Persons
, Not Selected

DELETE FROM Persons WHERE FirstName = ‘Peter’

DELETE FirstName=’Peter’ FROM Persons

A

DELETE FROM Persons WHERE FirstName = ‘Peter’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

With SQL, how can you return the number of records in the “Persons” table?

SELECT LEN(*) FROM Persons

SELECT NO(*) FROM Persons

SELECT COUNT(*) FROM Persons

SELECT COLUMNS(*) FROM Persons

A

SELECT COUNT(*) FROM Persons

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the most common type of join?

JOINED

INSIDE JOIN

INNER JOIN

JOINED TABLE

A

INNER JOIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

False

A

TRUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

With SQL, how can you insert a new record into the “Persons” table?

INSERT INTO Persons VALUES (‘Jimmy’, ‘Jackson’)

INSERT (‘Jimmy’, ‘Jackson’) INTO Persons

INSERT VALUES (‘Jimmy’, ‘Jackson’) INTO Persons

A

INSERT INTO Persons VALUES (‘Jimmy’, ‘Jackson’)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Which SQL statement is used to return only different values?

SELECT UNIQUE

SELECT DIFFERENT

SELECT DISTINCT

A

SELECT DISTINCT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Which operator is used to search for a specified pattern in a column?

GET

LIKE

FROM

A

LIKE

23
Q

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=’a’

SELECT * FROM Persons WHERE FirstName=’%a%’

SELECT * FROM Persons WHERE FirstName LIKE ‘a%’

SELECT * FROM Persons WHERE FirstName LIKE ‘%a’

A

SELECT * FROM Persons WHERE FirstName LIKE ‘a%’

24
Q

With SQL, how can you return all the records from a table named “Persons” sorted descending by “FirstName”?

SELECT * FROM Persons SORT BY ‘FirstName’ DESC

SELECT * FROM Persons ORDER FirstName DESC

SELECT * FROM Persons SORT ‘FirstName’ DESC

SELECT * FROM Persons ORDER BY FirstName DESC

A

SELECT * FROM Persons ORDER BY FirstName DESC

25
Q

With SQL, how do you select a column named “FirstName” from a table named “Persons”?

SELECT Persons.FirstName

SELECT FirstName FROM Persons

EXTRACT FirstName FROM Persons

A

SELECT FirstName FROM Persons

26
Q

What does SQL stand for?

Structured Question Language

Structured Query Language

Strong Question Language

A

Structured Query Language

27
Q

With SQL, how do you select all the records from a table named “Persons” where the “LastName” is alphabetically between (and including) “Hansen” and “Pettersen”?

SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’

SELECT LastName>’Hansen’ AND LastName<’Pettersen’ FROM Persons

SELECT * FROM Persons WHERE LastName>’Hansen’ AND LastName<’Pettersen’

A

SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’

28
Q

Which SQL statement is used to insert new data in a database?

INSERT INTO

ADD NEW

INSERT NEW

ADD RECORD

A

INSERT INTO

29
Q

Which SQL keyword is used to sort the result-set?

ORDER

SORT BY

SORT

ORDER BY

A

ORDER BY

30
Q

The NOT NULL constraint enforces a column to not accept NULL values.

True

False

A

True

31
Q

How to display all data from table

A

Select * From table_name

32
Q

how to display specific column from table

A

Select column_name from table_name

33
Q

How to display all data that meets where criteria. text is “ “ For example where star sign is “Capricorn”. This would display all records that meet this criteria

A

Select * From table_name Where column_name = “ “

34
Q

How to displays specific column that does not meet where criteria. Numbers without “ “ Text must have “ “

A

Select column_name From table_name Where column_name != “ “

35
Q

How to displays specific column that meets where criteria. Numbers without “ Text must have “ “

A

Select column_name From table_name Where column_name <= x

36
Q

How to display specific column that meets where criteria, less than or equal to a number

A

Select column_name From table_name Where column_name <= x

37
Q

How to display specific column that meets one of the where criteria

A

Select column_name From table_name Where column_name = “ “ OR column_name > x

38
Q

How to display all data from table and orders the data by column specified in ascending order

A

Select * from table_name Order By column_name

39
Q

How to display all records where a specific column data starts with letter T. Not LIKE does not need an = it uses ‘ ‘

A

Select * From table_name Where column_name Like ‘T%’

40
Q

Permanently updates Margot Robbies age to 30 in the celebrities table. Note if updating a number does not use “ “

A

Update Celebrities Set Age = 30 Where Name = “Margot Robbie”

41
Q

What does sqlite3.connect(‘database_name.sqlite’) do?

A

Create connection: Establish a connection to the database using sqlite3.connect(‘database_name.sqlite’). If the database doesn’t exist, it will be created.

42
Q

What does cursor.executemany?

A

Populate the database: Use cursor.executemany to insert multiple rows of data efficiently.

43
Q

What does cursor.execute?

A

Interact with data: Execute SQL queries using cursor.execute to retrieve specific data or filter based on conditions (e.g., SELECT * FROM table WHERE condition).

44
Q

What does USE database_name do?

A

Switch to the new database using USE database_name

45
Q

What does cursor.close() and conn.close() do?

A

Close connections: Always ensure you close the cursor and connection objects using cursor.close() and conn.close() to release resources.

46
Q

Filter the DataFrame to identify the row with the minimum publication year

A

(df[df[‘year_published’] == df[‘year_published’].min()]).

47
Q

what does .value_counts() and idxmax() do?

A

Use .value_counts() to find the frequency of books published in each decade.

Identify the decade with the highest count (idxmax()) and display it as the most popular decade.

48
Q

Explain the purpose of to_sql function.

A

to_sql method from pandas allows you to write the DataFrame contents into a new table within the SQLite database.

49
Q

What does the execute_query function do?

A

Establishes a connection to the database, executes the provided SQL query using pd.read_sql_query, retrieves the results as a DataFrame, closes the connection, and returns the DataFrame.

50
Q

what is Inner Join?

A

Inner Join: Retrieves rows where there’s a match in both tables (only matching rows are included).

51
Q

What is left join?

A

Left Join: Includes all rows from the left table and matching rows from the right table (missing values in the right table are filled with NULL).

52
Q

What is right join?

A

Right Join: Includes all rows from the right table and matching rows from the left table (missing values in the left table are filled with NULL).

53
Q

What is full join?

A

Full Join: Combines all rows from both tables, including unmatched rows with NULL values (provides a complete picture of the data).

54
Q

What is cross join?

A

Cross Join: Creates a Cartesian product, resulting in all possible combinations of rows from both tables (often used for generating all possible combinations).