I need this in MySql Workbench Write and execute a SELECT statement that deletes
ID: 3874033 • Letter: I
Question
I need this in MySql Workbench
Write and execute a SELECT statement that deletes records from the Jobs table WHERE StartingSalary is less than or equal to 40,000.
Update the unfilled jobs in the Jobs table to a value of filled. For example, set Filled to ‘True’ WHERE Filled = ‘False’.
Update the Starting Salary for JobID 1 to 50000.
Update the Starting Salary for all Software Developer jobs to 62000.
Delete all records from the jobs table where the JobTitle is equal to DataAnalyst.
Jobs table data: Please use this data for JobID, JobTitle, StartingSalary, Filled, and DateOpen. The JobID will be the primary key. CompanyID will be the foreign key.
JobID
(PK)
JobTitle
CompanyID
(FK)
StartingSalary
Filled
DateOpen
1
Database Administrator
10
40000
True
2016-05-12
2
Software Developer
1
45000
False
2016-10-09
3
Database Designer
5
42500
False
2016-11-01
4
Software Developer
4
41000
False
2016-09-14
5
Software Developer
2
60000
False
2016-10-31
6
Data Analyst
3
47000
False
2016-08-30
7
Database Administrator
6
38500
True
2016-08-22
8
Software Tester
9
40000
False
2016-11-02
9
Databse Designer
7
80000
False
2016-11-02
10
Data Analyst
8
97500
False
2016-10-15
JobID
(PK)
JobTitle
CompanyID
(FK)
StartingSalary
Filled
DateOpen
1
Database Administrator
10
40000
True
2016-05-12
2
Software Developer
1
45000
False
2016-10-09
3
Database Designer
5
42500
False
2016-11-01
4
Software Developer
4
41000
False
2016-09-14
5
Software Developer
2
60000
False
2016-10-31
6
Data Analyst
3
47000
False
2016-08-30
7
Database Administrator
6
38500
True
2016-08-22
8
Software Tester
9
40000
False
2016-11-02
9
Databse Designer
7
80000
False
2016-11-02
10
Data Analyst
8
97500
False
2016-10-15
Explanation / Answer
1.SELECT statement from the Jobs table WHERE StartingSalary is less than or equal to 40,000.
=>SELECT JobID, JobTitle, StartingSalary, Filled, and DateOpen FROM JOBS WHERE StartingSalary<=40000.
2. deletes records from the Jobs table WHERE StartingSalary is less than or equal to 40,000
=>DELETE FROM JOBS WHERE StartingSalary<=40000.
3. Update the unfilled jobs in the Jobs table to a value of filled. For example, set Filled to ‘True’ WHERE Filled = ‘False’.
=>UPDATE JOBS SET filled='true' where filled='false'.
3. Update the Starting Salary for JobID 1 to 50000.
=>UPDATE JOBS SET StartingSalary=50000 WHERE JobID=1.
4. Update the Starting Salary for all Software Developer jobs to 62000.
=>UPDATE JOBS SET StartingSalary=62000 WHERE JobTitle='Software Developer'.
5.Delete all records from the jobs table where the JobTitle is equal to DataAnalyst.
=>DELETE FROM JOBS WHERE JobTitle='Data Analyst'.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.