SQL statements

Question 1

What is used to make a db

Answer 1

CREATE DATABASE IF NOT EXISTS mydatabase

Question 2

Show your dbs

Answer 2

SHOW DATABASES

for x in cursor

Question 3

Make a table

Answer 3

CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))

Question 4

Show your tables

Answer 4

SHOW TABLES

for x in cursor

Question 5

Make a table and key

Answer 5

CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))

Question 6

Add prim keys to your table

Answer 6

ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY

Question 7

Add a record

Answer 7

INSERT INTO customers (name, address) VALUES (%s, %s)
cursor.execute(sql,val)

Question 8

Show number of rows last affected by execute

Answer 8

mycursor.rowcount

Question 9

Save changes

Answer 9

connection.commit

Question 10

ADd multiple rows

Answer 10

val = [()]

Question 11

Show latest record id inserted

Answer 11

mycursor.lastrowid

Question 12

Print all records from table

Answer 12

result = mycursor.fetchall()

for x in result

Question 13

Print name and addresses only from your table

Answer 13

SELECT name, address FROM customers

result = cursor.fetchall()
for x in result

Question 14

Get first row and print

Answer 14

SELECT * FROM customers

result = cursor.fetchone()

Question 15

Get addresses that have ‘simpson st” as address

Answer 15

SELECT * FROM customers WHERE address=’simpson st’
result = cursor.fetchall()

for x in myresult

Question 16

Get addresses that contain the word ‘way’

Answer 16

SELECT * FROM customers WHERE address LIKE=”%way%”

result=cursor.fetchall()

for x in result

Question 17

Sort everything by name in ascending order

Do it for descending

Answer 17

SELECT * FROM customers ORDER BY name

ORDER BY name DESC

Question 18

Delete records with address ‘mountain 21’

How do you delete everything

Answer 18

DELETE FROM customers WHERE address=’Mountain 21”

DELETE FROM customers

Question 19

Delete a table

Answer 19

DROP TABLE IF EXISTS customers

Question 20

EDIT address Canyon 123 to Valley 123

Answer 20

UPDATE customers SET address=”Canyon 123” WHERE address=’Valley 123’

Question 21

Get first 5 records

Answer 21

SELECT * FROM customers LIMIT 5

Question 22

Get 5 records starting from record 3

Answer 22

SELECT * FROM customers LIMIT 5 OFFSET 2

Question 23

You have two tables
One contains users names and a number that correlates to another table that contains favorite icecreams by an id number.

How do you combine fav and id?

Answer 23

import mysql.connector

mydb = mysql.connector.connect(
host=”localhost”,
user=”yourusername”,
password=”yourpassword”,
database=”mydatabase”
)

mycursor = mydb.cursor()

sql = “SELECT \
users.name AS user, \
products.name AS favorite \
FROM users \
INNER JOIN products ON users.fav = products.id”

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
print(x)

Question 24

What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN

Answer 24

INNER JOIN only shows the records where there is a match.

If you want to show all users, even if they do not have a favorite product, use the LEFT JOIN statement:

RIGHT JOIN If you want to return all products, and the users who have them as their favorite, even if no user have them as their favorite, use the RIGHT JOIN statemen

Question 25

Select all users and their favorite product

Answer 25

sql = “SELECT \
users.name AS user, \
products.name AS favorite \
FROM users \
LEFT JOIN products ON users.fav = products.id”