( 출처 : 연세대학교 데이터베이스 시스템 수업 (CSI6541) 강의자료 )
Chapter 5. Intermediate SQL (2)
(1) Create new User and Database (psql)
login to postegres
sudo -u postgre sql
Create new role & DB
CREATE USER myadmin WITH PASSWORD '1234';
CREATE DATABASE mydbase WITH OWNER='myadmin';
connect to new DB
\c mydbase myadmin
- revoke authentications of users…
- who are neither super roles / DB owner
REVOKE ALL ON DATABASE mydbase FROM PUBLIC;
connect to new DB (with new role)
\c mydbase myadmin
CREATE SCHEMA myschema AUTHORIZATION CURRENT_ROLE;
\q
(2) Create tables
CREATE TABLE IF NOT EXISTS myschema.department
(dept_name VARCHAR(20),
building VARCHAR(15),
budget NUMERIC(12,2) CHECK (budget > 0),
PRIMARY KEY (dept_name)
);
CREATE TABLE IF NOT EXISTS myschema.course
(course_id VARCHAR(8),
title VARCHAR(50),
dept_name VARCHAR(20),
credits NUMERIC(2,0) CHECK (credits > 0),
PRIMARY KEY (course_id),
FOREIGN KEY (dept_name) REFERENCES myschema.department (dept_name) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS myschema.instructor
(id VARCHAR(5),
name VARCHAR(20) NOT NULL,
dept_name VARCHAR(20),
salary NUMERIC(8,2) CHECK (salary > 29000),
PRIMARY KEY (id),
FOREIGN KEY (dept_name) REFERENCES myschema.department (dept_name) ON DELETE SET NULL
);
Drop tables ( if already exists ) & Create tables
drop_querys = """
DROP TABLE IF EXISTS "myschema"."course"
DROP TABLE IF EXISTS "myschema"."instructor"
DROP TABLE IF EXISTS "myschema"."department"
"""
create_query1 = """
CREATE TABLE IF NOT EXISTS myschema.department
(dept_name VARCHAR(20),
building VARCHAR(15),
budget NUMERIC(12,2) CHECK (budget > 0),
PRIMARY KEY (dept_name))
"""
create_query2 = """
CREATE TABLE IF NOT EXISTS myschema.course
(course_id VARCHAR(8),
title VARCHAR(50),
dept_name VARCHAR(20),
credits NUMERIC(2,0) CHECK (credits > 0),
PRIMARY KEY (course_id),
FOREIGN KEY (dept_name) REFERENCES myschema.department
(dept_name) ON DELETE SET NULL)
"""
with psycopg.connect(CONNECTION) as conn:
conn.execute(drop_querys)
conn.commit()
with psycopg.connect(CONNECTION) as conn:
conn.execute(create_query1)
conn.execute(create_query2)
conn.commit()
create_query_format = """
CREATE TABLE IF NOT EXISTS {table}(
{col1} VARCHAR(5),
{col2} VARCHAR(20) NOT NULL,
{col3} VARCHAR(20),
{col4} NUMERIC(0.2) CHECK ({col4}>29000),
PRIMARY KEY ({col1})
FOREIGN KEY ({col3}) REFERENCES {ref_table}({ref_column}) ON DELETE SET NULL
)
"""
select_query_format = """
SELECT "table_name"
FROM "information_schema"."tables"
WHERE "table_schema" = %s
ORDER BY "table_name";
"""
with psycopg.connect(CONNECTION) as conn:
conn.execute(
sql.SQL(create_query_format.format(
table = sql.Identifier('myschema','instructor'),
col1 = sql.Identifier('id'),
col2 = sql.Identifier('name'),
col3 = sql.Identifier('dept_name'),
col4 = sql.Identifier('salary'),
ref_table = sql.Identifier('myschema', 'department'),
ref_column = sql.Identifier('dept_name')
))
cur = conn.execute(select_query_format, ('myschema',))
results = cur.fetchall()
for result in results:
print(result)
conn.commit()
('course',)
('department',)
('instructor',)
(3) Insert records
INSERT INTO myschema.department VALUES ('Biology', 'Watson', '90000');
INSERT INTO myschema.department VALUES ('Comp. Sci.', 'Taylor', '100000');
INSERT INTO myschema.department VALUES ('Elec. Eng.', 'Taylor', '85000');
INSERT INTO myschema.department VALUES ('Finance', 'Painter', '120000');
INSERT INTO myschema.department VALUES ('History', 'Painter', '50000');
INSERT INTO myschema.department VALUES ('Music', 'Packard', '80000');
INSERT INTO myschema.department VALUES ('Physics', 'Watson', '70000');
insert_query_format = "INSERT INTO {} VALUES (%s, %s, %s)"
with psycopg.connect(CONNECTION) as conn:
conn.execute(sql.SQL("DELETE FROM {}").format(sql.Identifier('myschema', 'department')))
with conn.pipeline() as p:
query = sql.SQL(insert_query_format.format(
sql.Identifier('myschema', 'department')))
conn.execute(query, ('Biology', 'Watson', '90000'))
conn.execute(query, ('Comp. Sci.', 'Taylor', '100000'))
conn.execute(query, ('Elec. Eng', 'Taylor', '85000'))
conn.execute(query, ('Finance', 'Painter', '120000'))
conn.execute(query, ('History', 'Painter', '50000'))
conn.execute(query, ('Music', 'Packard', '80000'))
conn.execute(query, ('Physics', 'Watson', '70000'))
cur = conn.execute("""
SELECT * FROM "myschema"."department";
""")
results = cur.fetchall()
for result in results:
print(result)
conn.commit()
('Biology', 'Watson', '90000')
('Comp. Sci.', 'Taylor', '100000')
('Elec. Eng', 'Taylor', '85000')
('Finance', 'Painter', '120000')
('History', 'Painter', '50000')
('Music', 'Packard', '80000')
('Physics', 'Watson', '70000')
INSERT INTO myschema.course VALUES ('BIO-101', 'Intro. to Biology', 'Biology', '4');
INSERT INTO myschema.course VALUES ('BIO-301', 'Genetics', 'Biology', '4');
INSERT INTO myschema.course VALUES ('BIO-399', 'Computational Biology', 'Biology', '3');
INSERT INTO myschema.course VALUES ('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', '4');
INSERT INTO myschema.course VALUES ('CS-190', 'Game Design', 'Comp. Sci.', '4');
INSERT INTO myschema.course VALUES ('CS-315', 'Robotics', 'Comp. Sci.', '3');
INSERT INTO myschema.course VALUES ('CS-319', 'Image Processing', 'Comp. Sci.', '3');
INSERT INTO myschema.course VALUES ('CS-347', 'Database System Concepts', 'Comp. Sci.', '3');
INSERT INTO myschema.course VALUES ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', '3');
INSERT INTO myschema.course VALUES ('FIN-201', 'Investment Banking', 'Finance', '3');
INSERT INTO myschema.course VALUES ('HIS-351', 'World History', 'History', '3');
INSERT INTO myschema.course VALUES ('MU-199', 'Music Video Production', 'Music', '3');
INSERT INTO myschema.course VALUES ('PHY-101', 'Physical Principles', 'Physics', '4');
with psycopg.connect(CONNECTION) as conn:
conn.execute('DELETE FROM "myschema"."course"')
query = sql.SQL("INSERT INTO {} VALUES ({})").format(
sql.Identifier('myschema','course'),
sql.SQL(', ').join(sql.Placeholder() * 4)
)
with conn.pipeline() as p:
conn.execute(query, ('BIO-101', 'Intro. to Biology', 'Biology', '4'))
# ...
# ...
cur = conn.execute("""
SELECT *
FROM "myschema"."course"
""")
results = cur.fetchall()
for result in resuts:
print(result)
conn.commit()
INSERT INTO myschema.instructor VALUES ('10101', 'Srinivasan', 'Comp. Sci.', '65000');
INSERT INTO myschema.instructor VALUES ('12121', 'Wu', 'Finance', '90000');
INSERT INTO myschema.instructor VALUES ('15151', 'Mozart', 'Music', '40000');
INSERT INTO myschema.instructor VALUES ('22222', 'Einstein', 'Physics', '95000');
INSERT INTO myschema.instructor VALUES ('32343', 'El Said', 'History', '60000');
INSERT INTO myschema.instructor VALUES ('33456', 'Gold', 'Physics', '87000');
INSERT INTO myschema.instructor VALUES ('45565', 'Katz', 'Comp. Sci.', '75000');
INSERT INTO myschema.instructor VALUES ('58583', 'Califieri', 'History', '62000');
INSERT INTO myschema.instructor VALUES ('76543', 'Singh', 'Finance', '80000');
INSERT INTO myschema.instructor VALUES ('76766', 'Crick', 'Biology', '72000');
INSERT INTO myschema.instructor VALUES ('83821', 'Brandt', 'Comp. Sci.', '92000');
INSERT INTO myschema.instructor VALUES ('98345', 'Kim', 'Elec. Eng.', '80000');
insert_query_format = "INSERT INTO {0} ({1}) VALUES ({2})"
insert_colnames = ['id', 'name', 'dept_name', 'salary']
insert_contents = [('10101', 'Srinivasan', 'Comp. Sci.', '65000'),
('12121', 'Wu', 'Finance', '90000'),
('15151', 'Mozart', 'Music', '40000'),
('22222', 'Einstein', 'Physics', '95000'),
('32343', 'El Said', 'History', '60000'),
('33456', 'Gold', 'Physics', '87000'),
('45565', 'Katz', 'Comp. Sci.', '75000'),
('58583', 'Califieri', 'History', '62000'),
('76543', 'Singh', 'Finance', '80000'),
('76766', 'Crick', 'Biology', '72000'),
('83821', 'Brandt', 'Comp. Sci.', '92000'),
('98345', 'Kim', 'Elec. Eng.', '80000')]
insert_query = sql.SQL(insert_query_format).format(
sql.Identifier('myschema', 'instructor'),
sql.SQL(', ').join(map(sql.Identifier, insert_colnames)),
sql.SQL(', ').join(sql.Placeholder() * len(insert_colnames)) )
select_query_format = "SELECT * FROM {}"
select_query = sql.SQL(select_query_format).format(
sql.Identifier('myschema','instructor'))
with psycopg.connect(CONNECTION) as conn:
conn.execute("""
DELETE FROM "myschema"."instructor";
""")
with conn.pipeline() as p:
for data in insert_contents:
conn.execute(insert_query, data)
results = conn.execute(select_query).fetchall()
for result in results:
print(result)
conn.commit()
(4) Cartesian product
SELECT DISTINCT T.name, T.salary
FROM myschema.instructor AS T, myschema.instructor AS S
WHERE T.salary > S.salary AND S.dept_name = 'Comp. Sci.'
ORDER BY T.salary
select_query_format = """
SELECT DISTINCT T.{col1}, T.{col2}
FROM {tab1} AS T, {tab2} AS S
WHERE T.{col2} > S.{col2} AND S.{col3} = %s
ORDER BY T.{col2}
"""
with psycopg.connect(CONNECTION) as conn:
query = sql.SQL(select_query_format).format(
tab1 = sql.Identifier('myschema', 'instructor'),
col1 = sql.Identifier('name'),
col2 = sql.Identifier('salary'),
col3 = sql.Identifier('dept_name')
)
cur = conn.execute(query, ('Comp. Sci.',))
for record in cur.fetchall():
print(record)
(5) String operations
SELECT name
FROM myschema.instructor
WHERE name LIKE '%a%'
query_format = """
SELECT name
FROM myschema.instructor
WHERE name LIKE %s
"""
with psycopg.connect(CONNECTION) as conn:
cur = conn.execute(query_format, ('%%a%%',))
for record in cur.fetchall():
print(record)
(6) Where clause predicates
SELECT name
FROM myschema.instructor
WHERE salary BETWEEN 90000 AND 100000
query_format = """
SELECT name
FROM myschema.instructor
WHERE salary BETWEEN %s AND %s
"""
with psycopg.connect(CONNECTION) as conn:
cur = conn.execute(query_format, ('90000','100000'))
for record in cur.fetchall():
print(record)
(7) Set operations
(SELECT course_id, title
FROM myschema.course
WHERE dept_name = 'Comp. Sci.')
UNION
(SELECT course_id, title
FROM myschema.course
WHERE dept_name = 'Biology')
ORDER BY course_id
subquery1 = """
SELECT "course_id", "title"
FROM "myschema"."course"
WHERE "dept_name" = %s
"""
subquery2 = """
SELECT {}
FROM {}
WHERE {} = %s
""".format(
sql.SQL(', ').join(sql.Identifier('course_id'), sql.Identifier('title')),
sql.Identifier('myschema', 'course'),
sql.Identifier('dept_name')
)
with psycopg.connect(CONNECTION) as conn:
query = sql.SQL("({}) UNION ({})").format(subquery1, subquery1)
#query = sql.SQL("({}) UNION ({})").format(subquery2, subquery2)
query = sql.SQL("{} ORDER BY {}").format(query, sql.Identifier('course_id'))
cur = conn.execute(query, ('Comp. Sci.', 'Biology'))
for record in cur.fetchall():
print(record)
(8) Aggregate functions
SELECT dept_name, AVG(salary) AS avg_salary
FROM myschema.instructor
GROUP BY dept_name
select_query = """
SELECT "dept_name", AVG("salary") AS "avg_salary"
FROM "myschema"."instructor"
GROUP BY "dept_name"
"""
with psycopg.connect(CONNECTION) as conn:
cur = conn.execute(select_query)
for record in cur.fetchall():
print(record)
(9) Nested subqueries
SELECT name, salary
FROM myschema.instructor
WHERE salary > SOME(
SELECT salary
FROM myschema.instructor
WHERE dept_name = 'Comp. Sci.')
ORDER BY salary
main_query = """
SELECT "name", "salary"
FROM "myschema"."instructor"
WHERE "salary" > SOME({})
ORDER BY "salary"
"""
sub_query = """
SELECT "salary"
FROM "myschema"."instructor"
WHERE "dept_name" = %s
"""
with psycopg.connect(CONNECTION) as conn:
sub_query = sql.SQL(sub_query)
query = sql.SQL(main_query).format(sub_query)
cur = conn.execute(query, ('Comp. Sci.',))
for record in cur.fetchall():
print(record)
(10) Delete records
DELETE
FROM myschema.instructor
WHERE salary < (SELECT AVG(salary) FROM myschema.instructor)
main_query = """
DELETE
FROM "myschema"."instructor"
WHERE "salary" > ({})
"""
sub_query = """
SELECT AVG("salary")
FROM "myschema"."instructor"
"""
with psycopg.connect(CONNECTION) as conn:
sub_query = sql.SQL(sub_query)
query = sql.SQL(main_query).format(sub_query)
cur1 = conn.execute(query)
cur2 = conn.execute("""
SELECT *
FROM "myschema"."instructor";
""")
for record in cur2.fetchall():
print(record)
(11) Update records
UPDATE myschema.instructor
SET salary =
CASE
WHEN salary <= 100000 THEN salary * 1.05
ELSE salary * 1.03
END
main_query = """
UPDATE "myschema"."instructor"
SET "salary" = CASE {} END
"""
sub_query = """
WHEN "salary" <= 100000
THEN "salary" * 1.05
ELSE "salary" * 1.03
"""
with psycopg.connect(CONNECTION) as conn:
sub_query = sql.SQL(sub_query)
query = sql.SQL(main_query).format(sub_query)
cur1 = conn.execute(query)
cur2 = conn.execute("""
SELECT *
FROM "myschema"."instructor";
""")
for record in cur2.fetchall():
print(record)