Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

For how to: https://www.freecodecamp.org/news/how-to-install-mysql-workbench-on-windows/ Take a screen shots of your installation and paste here Watch this tutorial Section on how to use MYSQL work bench:

  1. For how to: https://www.freecodecamp.org/news/how-to-install-mysql-workbench-on-windows/
  2. Take a screen shots of your installation and paste here
  3. Watch this tutorial Section on how to use MYSQL work bench: https://www.youtube.com/watch?v=X_umYKqKaF0
Exercise 2 Provide answers to the following questions:
  1. Write the SQL code that will create the table structure for a table named EMP_1. This table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table (Note that the JOB_CODE is the FK to a relation JOB).
  1. Having created the table structure in Question a), write the SQL code to enter the following rows for the table shown as below.
  1. Assuming that the data shown in the EMP_1 table have been entered, write the SQL code that will list all attributes for a job code of 502.
  1. Write the SQL code to change the job code to 501 for the person whose personnel number is 106. After you have completed the task, examine the results, and then reset the job code to its original value.
  1. Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2002 and whose job code classification is 500.
  1. Write the SQL code to enter an EMP_PCT value of 3.85 for the person whose employee number (EMP_NUM) is 103. Next, enter the remaining EMP_PCT values as shown below.
  1. Using a single command sequence, write the SQL code that will enter the project number (PROJ_NUM) = 18 for all employees whose job classification (JOB_CODE) is 500.
  1. Using a single command sequence, write the SQL code that will enter the project number (PROJ_NUM) = 25 for all employees whose job classification (JOB_CODE) is 502 or higher.
  1. Write the SQL code that will enter a PROJ_NUM of 14 for those employees who were hired before January 1, 1992 and whose job code is at least 501.
  1. Write the SQL command that will delete the newly created TEMP_1 table from the database.
  1. Write the SQL code required to list all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing.
Week 8 Week 8 Lab/Tutorial To Be Submitted Given the following relational schema (the underlined attributes are the corresponding primary keys of the relation): STUDENT (id, name, dob, postalCode) SUBJECT (code, description) GRADE (id, code, score) Note: id and code in GRADE table are the foreign keys from STUDENT and SUBJECT tables. Design SQL queries that answer the questions given below (one query per question).
  1. Find all students whose grade is above the average grade.
  2. Find the number of students who score 78 and above.
  3. Find those students who do not score between 50 and 60 (inclusive).
  4. List the subject that has the lowest average score.
  5. List the highest score of each subject.
  6. Display the lowest score obtained by each student.
  7. Find the students who scored highest for each subject and display them in ascending order.
  8. Find the subject that has the highest number of students taking the exam.
  9. List the postal code of students who took subjects with average scores above 50.
  10. List the postal code of students who has at least 2 subjects scoring above 70.
Week 9 Week 9 Tutorial To be submitted You can type your answer directly in the SQL window in MySQL database, take a screenshot with the results shown; or directly write your answer in this file, but also attach the results screenshot. Create the following tables and populate the tables with data: STUDENT (STNO, SNAME, MAJOR, CLASS, BDATE) COURSE (COURSE_NAME, COURSE_NUMBER, CREDIT_HOURS, OFFERING_DEPT) DEPARTMENT_TO_MAJOR (Dcode, DNAME) GRADE_REPORT (STUDENT_NUMBER, SECTION_ID, GRADE) SECTION (SECTION_ID, COURSE_NUM, SEMESTER, YEAR, INSTRUCTOR, BLDG, ROOM) ROOM (BLDG, ROOM, CAPACITY, OHEAD) PREREQ (COURSE_NUMBER, PREREQ) DEPENDENT (PNO, DNAME, RELATIONSHIP, SEX, AGE)
  1. Show the results of the following SQL queries and explain what the purpose of the queries:
    1. SELECT * FROM Dependent;
    2. SELECT GETDATE();
    3. SELECT Add=4+3, Sub=4-3, Multi=4*3, Div=4/3;
    4. SELECT dname, age FROM Dependent WHERE age NOT BETWEEN 3 AND 5;
    5. SET ROWCOUNT 10 SELECT * FROM Dependent;
    6. CREATE VIEW namemaj AS SELECT sname, major FROM Student;
    7. SELECT * FROM namemaj;
    8. SELECT class, major, COUNT(*) AS [count] FROM Student GROUP BY class, major HAVING COUNT(*) > 2;
    9. SELECT class, COUNT(stno) AS [count of stno] FROM Student GROUP BY class HAVING COUNT(stno) = ( SELECT MIN(c) FROM ( SELECT COUNT(stno) AS c FROM Student GROUP BY class) AS inline_view);
  1. Write SQL queries for the following questions:
    1. Find the student names and dependent names of all the students who have dependents.
    2. Find the course names and the prerequisites of all courses that have prerequisites.
    3. Find out how many student names end with LD.
    4. Find out how many classes have more than 9 students.
Week 11 TO BE SUBMITTED DQ1: What are the trade-offs between consistency and performance when implementing transactions in a DBMS? DQ2: How does the implementation of transactions in a NoSQL database differ from a traditional relational database management system? DQ3: What are the common causes of transaction failures and how can they be prevented or addressed in a DBMS? DQ4: How does the design of a database schema affect the implementation of transactions and consistency in a DBMS? DQ5: What are the implications of using transactions in a microservices architecture and how can they be managed effectively in this context?

 



Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Mobile Communications

Authors: Jochen Schiller

2nd edition

978-0321123817, 321123816, 978-8131724262

More Books

Students also viewed these Programming questions