How do you use an INNER JOIN in SQL?
SELECT … FROM table1
INNER JOIN table2
ON table1.pk = table2.fk;
How do you connect MySQL to Python?
import mysql.connector
mydb = mysql.connector.connect(
host=”localhost”,
user=”annaho”,
passwd=”annahofs”)
How do you create a cursor and use it (Python)?
mycursor = mydb.cursor()
mycursor.execute(“SQL QUERY”)
How do you close the connection (Python)?
mycursor.close()
mydb.close()
How do you fetch data from the DB (Python)?
mycursor.fetchall()
mycursor.fetchone()
mycursor.fetchmany(size) #number of items to fetch
How do you INSERT data into SQL (Python)?
mycursor.execute(“INSERT INTO table_name VALUES (%s, %s , … , %s)”, (data1, data2, …, datan))
%s are placeholders
mydb.commit()
How do you use a LEFT/ RIGHT JOIN in SQL?
SELECT * FROM table_1
LEFT (RIGHT) JOIN table_2
ON table_1.pk = table_2.fk;
Left join returns all rows from first table with matches from second table
Right - vice versa
How do you use the UNION operator in SQL?
Use UNION operator between two select statements.
Returns all distinct rows.
How do you use a FULL JOIN in SQL?
SELECT * FROM table_1
FULL JOIN table_2
ON table_1.pk = table_2.fk;
All values from table 1 and table 2
How to INSERT into SQL?
INSERT INTO table_name
VALUES (attribute_value1, …, attribute_valuen);
Rows must be complete; use NULL for empty rows.
INSERT INTO table_name (attribute1, …, attributen)
VALUES (attribute_value1, …, attribute_valuen);
For auto inc pk: NULL
How to UPDATE data in SQL?
UPDATE table_name
SET attribute_target = expression1
WHERE attribute_select = expression2;
How do you DELETE data in SQL?
DELETE FROM table_name
WHERE attribute_name = expression;
What functions exist in SQL?
AVG(column)
COUNT(column) # returns no of rows without NULL
COUNT(*) # number of rows selected
MAX(column)
MIN(column)
SUM(column)
Can you use an aggregate function in a where command?
NO
How does HAVING work in SQL?
Used instead of WHERE for aggregate function conditions.
How do you use a subquery in SQL?
SELECT * FROM table1 WHERE attributeX {= | IN}
(SELECT attributeX
FROM table2
WHERE search_condition)
How do you create a View in SQL?
Create View Parking AS
SELECT Name, Telephone, Vehicle, Registration_ Number
FROM Employee, Parking_authorization
WHERE Employee.EID= Parking_authorization.EID;
How do you delete a view?
DROP VIEW view_name
How do you add a table in SQL?
ALTER TABLE customer ADD Lastorder datetime;
How do you create a trigger in SQL?
create trigger neworder
after insert on orders
for each row
update customer set Lastorder = now()
where customer.customer_id = new.customer_id;
How do you create a stored procedure in SQL?
CREATE PROCEDURE sp_mysp()
BEGIN
SELECT avg(salary) AS avgsal FROM employee;
update employee set salary = salary * 1.1 where empid < 3; SELECT avg(salary) AS avgsal FROM employee; END;
How do you call a stored procedure in SQL?
call sp_mysp();
