write SQL code to create relational schemas for below question?

Consider a database application which provides a web-based platform where students can practice on tasks assigned by the instructors. In terms of design, following are some major requirements: • Instructors will first create their accounts. Relevant details for the instructors are: email, affiliation and name. • Instructors will create Courses and add Students to each course. Relevant details for Courses are: course code, term and year. Relevant details for Students are: email and student ID. • In Courses, Instructors will create one or more assignment and an assignment will consist of one or more tasks. Each task has a grade. ITEC 3220 3 Students will work on Tasks and Assignments and will be awarded grade based on their submission.

write SQL code to create relational schemas for below question?

Assume your company is developing a generic digital camera online purchasing system that can be sold to different camera stores. An initial analysis phase of the project has resulted in the following informal description of relevant data for the system. A store will be selling a variety of digital cameras and lenses. The digital cameras can have following mutually exclusive optional features: 1. An ability to replace lenses. 2. Cameras with an electronic viewfinder. 3. Cameras with an optical viewfinder. 4. Cameras with an optical rangefinder. Relevant properties of all cameras are the model number, retail price and the number currently in stock. Properties of cameras without an ability to replace lenses that are relevant include a focal length range. Cameras with an ability to replace lenses are related to at least two or more lenses. Properties of a lens that are relevant include the model number, retail price and the number currently in stock. Online customers are either domestic customers or foreign customers. Properties of customers that are relevant include a unique customer number, a customer name, an email address and a shipping address. Each customer may have any number of purchase orders. Each purchase order is for only one unit of any product at a time, either a camera or a lens, and will also have a selling price.

                write SQL code for below question?

The professor number (pnum) and last name (lastname) of professors in the computer science (‘CS’) department (dept) who have taught a student in the course (cnum) ‘CS348’ whose final grade (grade) was less than 60. Winter 2023 ITEC 3220 3 2. The professor number (pnum) and last name (lastname) of each professor in the computer science (‘CS’) department (dept) who has taught neither course (cnum) ‘CS348’ nor ‘CS234’ at any time in the past. Assume current term (term) is ‘F2022’. 3. The professor number (pnum) and last name (lastname) of professors who have taught a ‘CS245’ section in which a student obtained a grade (grade) that is the lowest ever recorded for ‘CS245’. 4. The number (snum), name (firstname) and year (year) of each student who is fourth year (4) and who has a final grade (grade) of at least 90 in every course that he or she has completed in any computing department (cnum starts with letter C). B. For following query you may use aggregation 5. The percentage of professors who have taught or teaching at least two sections of a single course during the same term. Note that a percentage should be a number between 0 and 100, and that you can assume there is at least one professor. 

1.SQL code:

  SELECT DISTINCT prof.pnum, prof.lastname

  FROM professor AS Prof

  INNER JOIN Teaches AS T ON Prof.pnum = T.pnum

  INNER JOIN Takes AS Ta ON T.cnum = Ta.cnum AND T.term = Ta.term AND T.secnum = Ta.secnum

 WHERE Ta.cnum = ‘CS348’ AND Ta.grade < 60 AND Prof.dept = ‘CS’ ;

2.. SQL code:

         SELECT DISTINCT Prof.pnum, Prof.lastname

         FROM Professor AS Prof

         WHERE Prof.dept = ‘CS’ AND Prof.pnum NOT IN (

         SELECT DISTINCT T.pnum

         FROM Teaches AS T

WHERE T.cnum = ‘CS348’ OR T.CNUM = ‘CS234’

);

3. SQL code:

         SELECT DISTINCT T.pnum, prof.lastname

         FROM Teaches AS T

         INNER JOIN Takes AS Ta ON T.cnum = Ta.cnum AND T.term = Ta.term AND T.secnum = Ta.secnum

         INNER JOIN Professor AS Prof ON T.pnum = Prof.pnum

         WHERE T.cnum = ‘CS245’ AND Ta.grade = ( SELECT MIN(Takes.grade)

         FROM Takes

         WHERE Takes.cnum = ‘CS245’

);

4. SQL code:

         SELECT DISTINCT S.snum, S.firstname, S.year

         FROM Student AS S

         INNER JOIN Takes AS Ta ON S.snum = Ta.snum

         INNER JOIN Course AS C ON Ta.cnum = C.cnum

         WHERE S.year = 4 AND C.cnum LIKE ‘C%’ AND NOT EXISTS (

         SELECT *

         FROM Takes AS T

WHERE T.snum = S.snum AND T.grade <  90

);

From the above Four SQL Queries and Relational algebra about professors and students based conditions.

First query finds professor on CS department who taught a CS348 student with grade below 60.

Second Query finds CS Professors who haven’t taught CS348 OR CS234.

Third query professor who taught a CS245 section where a student scored the lowest ever grade for the course.

Fourth query finds the final year student who scored at lease 90 in all computing course that they completed.