129 lines
2.0 KiB
Python
129 lines
2.0 KiB
Python
import sys
|
|
|
|
# Use this file to write your queries. Submit this file in Gradescope after finishing your homework.
|
|
|
|
#TODO: Write your username and answer to each query as a string in the return statements in the functions below.
|
|
# Do not change the function names.
|
|
|
|
#Make sure to test that python prints out the strings (your username and SQL queries) correctly.
|
|
|
|
#usage:python3 hw1.py
|
|
# make sure the program prints out your SQL statements correctly. That means the autograder will read you SQL correctly.
|
|
# Running the Python3 file will not execute your SQL statements (just prints them).
|
|
|
|
def username():
|
|
return "srettig" # Write your username here
|
|
|
|
|
|
def query1():
|
|
return """
|
|
SELECT
|
|
Fname,
|
|
Minit,
|
|
Lname
|
|
FROM
|
|
cs448_employee ce
|
|
WHERE
|
|
(
|
|
SELECT
|
|
COUNT(*)
|
|
FROM
|
|
cs448_dependent cd
|
|
WHERE
|
|
ce.Ssn = cd.Essn
|
|
) >= 3
|
|
AND (
|
|
SELECT
|
|
SUM(Hours)
|
|
FROM
|
|
cs448_works_on cwo
|
|
WHERE
|
|
ce.Ssn = cwo.Essn
|
|
) >= 30;
|
|
"""
|
|
|
|
def query2():
|
|
return """
|
|
UPDATE
|
|
cs448_dept_location
|
|
Set
|
|
Dlocation = 'Lafayette'
|
|
WHERE
|
|
Dnumber in
|
|
(
|
|
SELECT
|
|
Dnumber
|
|
From
|
|
cs448_department cd
|
|
WHERE
|
|
(
|
|
SELECT
|
|
COUNT(*)
|
|
FROM
|
|
cs448_employee ce
|
|
WHERE
|
|
cd.Dnumber = ce.Dnumber
|
|
) < 5)
|
|
"""
|
|
|
|
def query3():
|
|
return """
|
|
DELETE
|
|
FROM
|
|
cs448_dependent
|
|
WHERE
|
|
Essn in
|
|
(
|
|
SELECT
|
|
ce.Ssn
|
|
From
|
|
cs448_employee ce,
|
|
cs448_employee cem
|
|
WHERE
|
|
ce.Super_ssn = cem.Ssn
|
|
AND ce.Dnumber != cem.Dnumber)
|
|
"""
|
|
|
|
def query4():
|
|
return """
|
|
SELECT
|
|
Fname,
|
|
Minit,
|
|
Lname
|
|
From
|
|
cs448_employee
|
|
WHERE
|
|
(Salary,
|
|
Ssn) IN (
|
|
SELECT
|
|
MAX(Salary),
|
|
Ssn
|
|
FROM
|
|
cs448_employee ce2
|
|
Group BY
|
|
ce2.Dnumber)
|
|
"""
|
|
|
|
#Do not edit below
|
|
|
|
def main():
|
|
query_options = {1: query1(), 2: query2(), 3: query3(), 4: query4()}
|
|
|
|
if len(sys.argv) == 1:
|
|
if username() == "username":
|
|
print("Make sure to change the username function to return your username.")
|
|
return
|
|
else:
|
|
print(username())
|
|
for query in query_options.values():
|
|
print(query)
|
|
elif len(sys.argv) == 2:
|
|
if sys.argv[1] == "username":
|
|
print(username())
|
|
else:
|
|
print(query_options[int(sys.argv[1])])
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|