SQL

Question 1

Returning first 10 rows in descinding order of Col_A from a table in SQLite filtered for when Col_A or Col_B are less than zero

Answer 1

SELECT *
FROM Table_Name
WHERE Col_A <0
     OR Col_B <0
ORDER BY Col_A DESC
LIMIT 10;

Question 2

Returning a count of rows in a table

Answer 2

SELECT COUNT(Column_Name)
FROM Table_Name;

Question 3

Returning rows that match a list of values

Answer 3

SELECT *
FROM table
WHERE
column_name IN (‘value1’, ‘value2’, value3, ‘value4’);

Question 4

Creating a view

Answer 4

CREATE VIEW View_name AS

SELECT * FROM Table_name;

Question 5

Delete a view

Answer 5

DROP VIEW View_Name;

Question 6

Append two select statements

Answer 6

[select_statement_one]
UNION
[select_statement_two];

Question 7

Selecting rows that occur in both SELECT statements

Answer 7

[select_statement_one]
INTERSECT
[select_statement_two];

Question 8

Selecting rows that occur in the first SELECT statement but not the second SELECT statement

Answer 8

[select_statement_one]
EXCEPT
[select_statement_two];

Question 9

Launching the SQLite shell

Answer 9

sqlite3 chinook.db

Question 10

Activate column headers in SQLite shell

Answer 10

.headers on

Question 11

Activate column formatting for SQLite shell output

Answer 11

.mode column

Question 12

Displaying help text in SQLite shell

Answer 12

.help

Question 13

Display the list of tables and view of a database from SQLite shell

Answer 13

.tables

Question 14

Run a BASH command from within SQLite shell

Answer 14

.shell [BASH_Command]

Question 15

View a table’s schema from within SQLite shell

Answer 15

.schema [Table_Name]

Question 16

Terminate the SQLite shell

Answer 16

.quit

Question 17

Creating a table with a primary key and foreign key

Answer 17

CREATE TABLE [table_name] (
     [pk_id_name]  INTEGER PRIMARY KEY,
     [fk_id_name] INTEGER,
     [column3_name] [column3_type],
     FOREIGN KEY ([fk_id_name]) REFERENCES [fk_table]([fk_id_name])
);

Question 18

Creating a table with a compound primary key and multiple foreign keys

Answer 18

CREATE TABLE [table_name] (
[pk1_id_name] INTEGER,
[pk2_id_name] INTEGER,
[fk1_id_name] INTEGER,
[column3_name] [column3_type],
PRIMARY KEY ([pk1_id_name], [pk2_id_name]),
FOREIGN KEY ([fk1_id_name]) REFERENCES fk1_table,
FOREIGN KEY ([fk2_id_name]) REFERENCES fk2_table
);

Question 19

Insert a new row of values into certain columns of an existing table

Answer 19

INSERT INTO [table_name] (
     [column1_name],
     [column2_name],
     [column3_name]
) VALUES (
     [value1],
     [value2],
     [value3]
);

Question 20

Insert multiple rows for all columns of an existing table

Answer 20

INSERT INTO [table_name]
VALUES
([A1], [A2], [A3]),
(B1], [B2], [B3]);

Question 21

Deleting selected rows from a table

Answer 21

DELETE FROM [table_name]

WHERE [filter_expression];

Question 22

Create a new column in an existing table

Answer 22

ALTER TABLE [table_name]

ADD COLUMN [column_name] [column_type];

Question 23

Changing values for existing rows

Answer 23

UPDATE [table_name]
SET 
     [column1] = [expression],
     [column1] = [expression]
WHERE [expression];

Question 24

Import Python module for working with SQLite 3

Answer 24

import sqlite3

Question 25

Use Python to create a connection to a SQLite database

Answer 25

conn = sqlite3.connect(“job.db”)

Question 26

Create a Python Cursor class

Answer 26

cursor = conn.cursor()

Question 27

Run a SQL query from a Python cursor

Answer 27

cursor.execute(“SQL statement as string;”)

Question 28

Fetch the full results as a list of tuples from a Python cursor

Answer 28

results = cursor.fetchall()

Question 29

Fetch the one results from a Python cursor and then another

Answer 29

first_result = cursor.fetchone()
second_result = cursor.fetchone()

Question 30

Fetch the first five results from a Python cursor

Answer 30

five_results = cursor.fetchmany(5)

Question 31

Terminate a Python SQLite3 connection

Answer 31

conn.close()