# Assingment 27
from mysql.connector import (connection)

cnx = connection.MySQLConnection(user='vishal', password='12345',host='localhost',database='db1')
mycursor = cnx.cursor()
mycursor.execute("create table Users(Emailid varchar(20), pass varchar(20), usertype varchar(20))")
mycursor.execute("create table job_Posted(Jobpostid varchar(20), companyid varchar(20), jobpostdate varchar(20), validityDate varchar(20), joblocation varchar(20), skillsets varchar(20), yrsofexp varchar(20), qualifyingtestscore varchar(20), jobpostedstatus varchar(20))")
mycursor.execute("create table Jobseeker_Personal(firstname varchar(20), lastname varchar(20), dateofbirth varchar(20), emailid varchar(20), mobile varchar(20), city varchar(20), state varchar(20), country varchar(20), passportno varchar(20))")
mycursor.execute("create table Jobseeker_Resume(jobseekerid varchar(20), emailid varchar(20), ug_qualification varchar(20), pg_qualification varchar(20), keyskills1 varchar(20), keyskills2 varchar(20), additionalskills varchar(20), yrsofexp varchar(20), curremployer varchar(20), designation varchar(20), curr_CTC varchar(20), exp_CTC varchar(20))")
mycursor.execute("create table Employer(companyid varchar(20), companyname varchar(20), emailid varchar(20), mobile varchar(20), city varchar(20), industrytype varchar(20), functionalarea varchar(20), membershipplan varchar(20), dateofsignup varchar(20), dateofrenewal varchar(20), renewalstatus varchar(20))")
mycursor.execute("create table Job_Applied(applicationid varchar(20), jobseekerid varchar(20), jobpostid varchar(20), applicationdate varchar(20), jobtestvalidityDate varchar(20), Attempt varchar(20), jobteststatus varchar(20), jobtestscore varchar(20), jobtestresult varchar(20))")

#(i)
mycursor.execute("select 'firstname' from Jobseeker_Personal")
# (ii)
mycursor.execute("select * from Job_Posted where 'yrsofexp'<=2")
mycursor.execute("select * from Job_Posted where 'yrsofexp'>=2 and 'yrsofexp'<8")
mycursor.execute("select * from Job_Posted where 'yrsofexp'>=8")
#(iii)
mycursor.execute("select 'jobseekerid' from Jobseeker_Resume where ORDER BY 'designation' and 'exp_ctc'>1000000")
#(iv)
mycursor.execute("select 'jobpostid', MONTH('jobpostdate') from Job_Posted")
#(v)
mycursor.execute("select 'companyid' from Employer where MONTH('dateofrenewal')<='dec'")
#(vi)
mycursor.execute("select * from jobseeker_Resume where (('curr_CTC'/'exp_CTC')*100)>=30")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)
cnx.commit()
mycursor.close()
cnx.close()