Answered step by step
Verified Expert Solution
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:
- 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: https://www.youtube.com/watch?v=X_umYKqKaF0
- 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).
- Having created the table structure in Question a), write the SQL code to enter the following rows for the table shown as below.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- Write the SQL command that will delete the newly created TEMP_1 table from the database.
- 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.
- Find all students whose grade is above the average grade.
- Find the number of students who score 78 and above.
- Find those students who do not score between 50 and 60 (inclusive).
- List the subject that has the lowest average score.
- List the highest score of each subject.
- Display the lowest score obtained by each student.
- Find the students who scored highest for each subject and display them in ascending order.
- Find the subject that has the highest number of students taking the exam.
- List the postal code of students who took subjects with average scores above 50.
- List the postal code of students who has at least 2 subjects scoring above 70.
- Show the results of the following SQL queries and explain what the purpose of the queries:
- SELECT * FROM Dependent;
- SELECT GETDATE();
- SELECT Add=4+3, Sub=4-3, Multi=4*3, Div=4/3;
- SELECT dname, age FROM Dependent WHERE age NOT BETWEEN 3 AND 5;
- SET ROWCOUNT 10 SELECT * FROM Dependent;
- CREATE VIEW namemaj AS SELECT sname, major FROM Student;
- SELECT * FROM namemaj;
- SELECT class, major, COUNT(*) AS [count] FROM Student GROUP BY class, major HAVING COUNT(*) > 2;
- 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);
- Write SQL queries for the following questions:
- Find the student names and dependent names of all the students who have dependents.
- Find the course names and the prerequisites of all courses that have prerequisites.
- Find out how many student names end with LD.
- Find out how many classes have more than 9 students.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started