Task - DML Commands - MySQL Restricting and Sorting data #17

akash-coded · 2022-07-01T09:47:21Z

akash-coded
Jul 1, 2022
Maintainer

Database = `hr`

Dump file: hr.sql

DB Schema

Queries

Write a query to display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000.
Write a query to display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000 and are in department 30 or 100.
Write a query to display the name (first_name, last_name) and hire date for all employees who were hired in 1987.
Write a query to display the first_name of all employees who have both "b" and "c" in their first name.
Write a query to display the last name, job, and salary for all employees whose job is that of a Programmer or a Shipping Clerk, and whose salary is not equal to $4,500, $10,000, or $15,000.
Write a query to display the last name of employees having 'e' as the third character.
Write a query to display the last name of employees whose names have exactly 6 characters.
Write a query to select all record from employees where last name in 'BLAKE', 'SCOTT', 'KING' and 'FORD'.

gayatribabar · 2022-07-01T10:32:32Z

gayatribabar
Jul 1, 2022

Question 1

Question 2

mysql> SELECT first_name, last_name, salary, department_id FROM employees WHERE salary NOT BETWEEN 10000 AND 15000 and department_id IN(30,100) limit 10;
+-------------+------------+---------+---------------+
| first_name | last_name | salary | department_id |
+-------------+------------+---------+---------------+
| Alexander | Khoo | 3100.00 | 30 |
| Shelli | Baida | 2900.00 | 30 |
| Sigal | Tobias | 2800.00 | 30 |
| Guy | Himuro | 2600.00 | 30 |
| Karen | Colmenares | 2500.00 | 30 |
| Daniel | Faviet | 9000.00 | 100 |
| John | Chen | 8200.00 | 100 |
| Ismael | Sciarra | 7700.00 | 100 |
| Jose Manuel | Urman | 7800.00 | 100 |
| Luis | Popp | 6900.00 | 100 |
+-------------+------------+---------+---------------+
10 rows in set (0.00 sec)

Question 3

mysql> select first_name, hire_date from employees where substring(hire_date,1,4)=1987;
+-------------+------------+
| first_name | hire_date |
+-------------+------------+
| Steven | 1987-06-17 |
| Neena | 1987-06-18 |
| Lex | 1987-06-19 |
| Alexander | 1987-06-20 |
| Bruce | 1987-06-21 |
| David | 1987-06-22 |
| Valli | 1987-06-23 |
| Diana | 1987-06-24 |
| Nancy | 1987-06-25 |
| Daniel | 1987-06-26 |
| John | 1987-06-27 |
| Ismael | 1987-06-28 |
| Jose Manuel | 1987-06-29 |
| Luis | 1987-06-30 |
| Den | 1987-07-01 |
| Alexander | 1987-07-02 |
| Shelli | 1987-07-03 |
| Sigal | 1987-07-04 |
| Guy | 1987-07-05 |
| Karen | 1987-07-06 |
| Matthew | 1987-07-07 |
| Adam | 1987-07-08 |
| Payam | 1987-07-09 |
| Shanta | 1987-07-10 |
| Kevin | 1987-07-11 |
| Julia | 1987-07-12 |
| Irene | 1987-07-13 |
| James | 1987-07-14 |
| Steven | 1987-07-15 |
| Laura | 1987-07-16 |
| Mozhe | 1987-07-17 |
| James | 1987-07-18 |
| TJ | 1987-07-19 |
| Jason | 1987-07-20 |
| Michael | 1987-07-21 |
| Ki | 1987-07-22 |
| Hazel | 1987-07-23 |
| Renske | 1987-07-24 |
| Stephen | 1987-07-25 |
| John | 1987-07-26 |
| Joshua | 1987-07-27 |
| Trenna | 1987-07-28 |
| Curtis | 1987-07-29 |
| Randall | 1987-07-30 |
| Peter | 1987-07-31 |
| John | 1987-08-01 |
| Karen | 1987-08-02 |
| Alberto | 1987-08-03 |
| Gerald | 1987-08-04 |
| Eleni | 1987-08-05 |
| Peter | 1987-08-06 |
| David | 1987-08-07 |
| Peter | 1987-08-08 |
| Christopher | 1987-08-09 |
| Nanette | 1987-08-10 |
| Oliver | 1987-08-11 |
| Janette | 1987-08-12 |
| Patrick | 1987-08-13 |
| Allan | 1987-08-14 |
| Lindsey | 1987-08-15 |
| Louise | 1987-08-16 |
| Sarath | 1987-08-17 |
| Clara | 1987-08-18 |
| Danielle | 1987-08-19 |
| Mattea | 1987-08-20 |
| David | 1987-08-21 |
| Sundar | 1987-08-22 |
| Amit | 1987-08-23 |
| Lisa | 1987-08-24 |
| Harrison | 1987-08-25 |
| Tayler | 1987-08-26 |
| William | 1987-08-27 |
| Elizabeth | 1987-08-28 |
| Sundita | 1987-08-29 |
| Ellen | 1987-08-30 |
| Alyssa | 1987-08-31 |
| Jonathon | 1987-09-01 |
| Jack | 1987-09-02 |
| Kimberely | 1987-09-03 |
| Charles | 1987-09-04 |
| Winston | 1987-09-05 |
| Jean | 1987-09-06 |
| Martha | 1987-09-07 |
| Girard | 1987-09-08 |
| Nandita | 1987-09-09 |
| Alexis | 1987-09-10 |
| Julia | 1987-09-11 |
| Anthony | 1987-09-12 |
| Kelly | 1987-09-13 |
| Jennifer | 1987-09-14 |
| Timothy | 1987-09-15 |
| Randall | 1987-09-16 |
| Sarah | 1987-09-17 |
| Britney | 1987-09-18 |
| Samuel | 1987-09-19 |
| Vance | 1987-09-20 |
| Alana | 1987-09-21 |
| Kevin | 1987-09-22 |
| Donald | 1987-09-23 |
| Douglas | 1987-09-24 |
| Jennifer | 1987-09-25 |
| Michael | 1987-09-26 |
| Pat | 1987-09-27 |
| Susan | 1987-09-28 |
| Hermann | 1987-09-29 |
| Shelley | 1987-09-30 |
| William | 1987-10-01 |
+-------------+------------+
107 rows in set (0.00 sec)

Question 4

mysql> select first_name from employees where first_name like '%b%' and first_name like '%c%' limit 10;
+------------+
| first_name |
+------------+
| Bruce |
+------------+
1 row in set (0.00 sec)

Question 5

Question 6

Question 7

Question 8

mysql> select * from employees where last_name IN ( 'BLAKE', 'SCOTT', 'KING', 'FORD');
+-------------+------------+-----------+-------+--------------------+------------+---------+----------+----------------+------------+---------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID |
+-------------+------------+-----------+-------+--------------------+------------+---------+----------+----------------+------------+---------------+
| 156 | Janette | King | JKING | 011.44.1345.429268 | 1987-08-12 | SA_REP | 10000.00 | 0.35 | 146 | 80 |
| 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 |
+-------------+------------+-----------+-------+--------------------+------------+---------+----------+----------------+------------+---------------+
2 rows in set (0.00 sec)

0 replies

ghost · 2022-07-01T10:51:48Z

ghost
Jul 1, 2022

1. SELECT first_name, last_name, salary FROM employees WHERE salary NOT BETWEEN 10000 AND 15000;
2.  SELECT first_name, last_name, salary FROM employees WHERE salary NOT BETWEEN 10000 AND 15000 AND department_id IN
 (30, 100);
3. SELECT first_name, last_name, hire_date FROM employees WHERE TO_CHAR(hire_date, 'YYYY')  LIKE '%87';
4. SELECT first_name FROM employees WHERE first_name LIKE '%b%' AND first_name LIKE '%c%';
5. SELECT last_name, job_id, salary FROM employees WHERE job_id IN ('IT_PROG', 'SH_CLERK') AND salary NOT IN (4500,10000, 15000);
6. SELECT last_name FROM employees WHERE last_name LIKE '__e%';
7. SELECT last_name FROM employees WHERE last_name LIKE '______';
8. SELECT * FROM employees WHERE last_name IN('JONES', 'BLAKE', 'SCOTT', 'KING', 'FORD');

0 replies

meghvi-raj · 2022-07-01T10:54:34Z

meghvi-raj
Jul 1, 2022

--Q1
select concat(first_name, " ", last_name) as "Name",
    salary
from employees
where salary < 10000
    OR salary > 15000;

--Q2
select concat(first_name, " ", last_name) as "Name",
    salary,
    department_id
from employees
where (
        salary < 10000
        OR salary > 15000
    )
    AND department_id in (30, 100);

--Q3
select concat(first_name, " ", last_name) as "Name",
    hire_date
from employees
where hire_date like "1987%";

--Q4
select first_name
from employees
where first_name like "%b%"
    or first_name like "%c%";

--Q5
select last_name,
    salary,
    job_id
from employees
where job_id in ("IT_PROG", "SH_CLERK")
    AND salary not in (4500, 10000, 15000);

--Q6

select last_name
from employees
where last_name like "__e%";

--Q7
select last_name
from employees
where length(last_name) = 3;
--Q8

select *
from employees
where last_name in ("Blake", "Scott", "King", "Ford");

0 replies

vshvandana · 2022-07-01T10:55:21Z

vshvandana
Jul 1, 2022

select CONCAT(first_name," ",last_name) AS names, salary from employees where salary NOT BETWEEN 10000 and 15000;
select CONCAT(first_name," ",last_name) AS names, salary from employees where salary NOT BETWEEN 10000 and 15000 AND DEPARTMENT_ID IN(30,100);
select CONCAT(first_name," ",last_name) AS names, HIRE_DATE from employees where HIRE_DATE LIKE "1987%";
select first_name from employees where first_name LIKE '%b%' and first_name LIKE'%c%';
select last_name, salary, job_id from employees where job_id in ("IT_PROG","SH_CLERK") AND salary not in (4500,10000,15000);
select last_name from employees where last_name like '__e%';
select last_name from employees where last_name LIKE '______';
select * from employees where last_name IN('BLAKE','SCOTT','KING','FORD');

0 replies

uzma23 · 2022-07-01T10:58:00Z

uzma23
Jul 1, 2022

-- Write a query to display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000.
SELECT concat(first_name, " ", last_name),SALARY
FROM employees
WHERE SALARY NOT BETWEEN 10000 AND 15000;

-- Write a query to display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000 and are in department 30 or 100.
SELECT concat(first_name, " ", last_name),SALARY
FROM employees
WHERE SALARY NOT BETWEEN 10000 AND 15000 AND DEPARTMENT_ID=30;

-- Write a query to display the name (first_name, last_name) and hire date for all employees who were hired in 1987.
SELECT concat(first_name, " ", last_name) name, HIRE_DATE
FROM employees
WHERE HIRE_DATE LIKE "1987%"

-- Write a query to display the first_name of all employees who have both "b" and "c" in their first name.
SELECT concat(first_name, " ", last_name) AS "name"
FROM employees
WHERE FIRST_NAME LIKE "%B%" OR FIRST_NAME LIKE "%C%"

-- Write a query to display the last name, job, and salary for all employees whose job is that of a Programmer or a Shipping Clerk, and whose salary is not equal to $4,500, $10,000, or $15,000.
select last_name, salary, job_id from employees

where job_id in ("IT_PROG","SH_CLERK") AND salary not in (4500,10000,15000);

-- Write a query to display the last name of employees having 'e' as the third character.
SELECT last_name
FROM employees
WHERE LAST_NAME LIKE "__e%" 


-- Write a query to display the last name of employees whose names have exactly 6 characters.
SELECT  last_name
FROM employees
WHERE LAST_NAME LIKE "______"

-- Write a query to select all record from employees where last name in 'BLAKE', 'SCOTT', 'KING' and 'FORD'.

SELECT concat(first_name, " ", last_name) AS "name"
FROM employees
WHERE LAST_NAME IN ('BLAKE', 'SCOTT', 'KING' and 'FORD')

0 replies

Coder-093 · 2022-07-01T11:01:11Z

Coder-093
Jul 1, 2022

select * from employees;

select first_name, last_name, salary from employees where salary not between 10000 and 15000;

select first_name, last_name, salary, department_id from employees where salary not between 10000 and 15000 and department_id in(30,100);

select first_name, last_name, hire_date from employees where year(hire_date) like 1987;

select first_name from employees where first_name like '%b%' and first_name like '%c%';

select * from jobs;

select e.last_name, j.job_title, e.salary from employees e, jobs j where  job_title in ('Programmer', 'Shipping Clerk') and salary not in(4500,10000,15000);

select last_name from employees where last_name like '__e%';

select last_name from employees where last_name like '______';

select * from employees where last_name in ('blake','scott','king','ford');

0 replies

ghost · 2022-07-01T11:01:25Z

ghost
Jul 1, 2022

select first_name,
    last_name,
    salary
from employees
where salary not between 10000 and 15000;

select first_name,
    last_name,
    salary
from employees
where salary not between 10000 and 15000
    and DEPARTMENT_ID in(30, 100);

select first_name,
    last_name,
    salary
from employees
where HIRE_DATE like '1987%';

select first_name
from employees
where FIRST_NAME LIKE '%b%'
    or '%c%';

SELECT last_name,
    job_id,
    salary
FROM employees
WHERE job_id IN ('IT_PROG', 'SH_CLERK')
    AND salary NOT IN (4500, 10000, 15000);

SELECT LAST_NAME
from employees
where LAST_NAME LIKE '__e%';

select LAST_NAME
FROM employees
where LENGTH(LAST_NAME) = 6;

select *
from employees
where LAST_NAME in ('BLAKE', 'SCOTT', 'KING', 'FORD');

0 replies

Kuntapaliwal · 2022-07-02T14:38:58Z

Kuntapaliwal
Jul 2, 2022

-- Write a query to display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000.
SELECT CONCAT(first_name, " ", last_name) as name,
    salary
from employees
where salary NOT IN(10000, 15000);



-- Write a query to display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000 and are in department 30 or 100.
SELECT CONCAT(first_name, " ", last_name) as name,
    salary
from employees
where salary NOT IN(10000, 15000)
    AND DEPARTMENT_ID = 30
    OR DEPARTMENT_ID = 100;
-- or
SELECT CONCAT(first_name, " ", last_name) as name,
    salary
from employees
where salary NOT IN(10000, 15000)
    AND DEPARTMENT_ID IN(30, 100);


-- Write a query to display the name (first_name, last_name) and hire date for all employees who were hired in 1987.
SELECT CONCAT(first_name, " ", last_name) as name,
    hire_date
from employees
where year(HIRE_DATE) LIKE "1987";


-- Write a query to display the first_name of all employees who have both "b" and "c" in their first name.
SELECT first_name
from employees
where first_name LIKE '%b%'
    AND first_name LIKE '%c%';


-- Write a query to display the last name, job, and salary for all employees whose job is that of a Programmer or a Shipping Clerk, and whose salary is not equal to $4,500, $10,000, or $15,000.
SELECT last_name,
    job_id,
    salary
from employees
where job_id = 'IT_PROG'
    OR job_id = 'SH_CLERK'
    AND salary <> 4500
    AND salary <> 10000
    AND SALARY <> 15000;
-- or
SELECT last_name,
    job_id,
    salary
FROM employees
WHERE job_id IN ('IT_PROG', 'SH_CLERK')
    AND salary NOT IN (4500, 10000, 15000);


-- Write a query to display the last name of employees having 'e' as the third character.
SELECT last_name
from employees
where LAST_NAME LIKE '__e%';


-- Write a query to display the last name of employees whose names have exactly 6 characters.
SELECT last_name
FROM employees
WHERE last_name LIKE '______';


-- Write a query to select all record from employees where last name in 'BLAKE', 'SCOTT', 'KING' and 'FORD'.
SELECT *
FROM employees
WHERE last_name IN('BLAKE', 'SCOTT', 'KING', 'FORD');

0 replies

ApoorvaSinha · 2022-07-04T04:01:48Z

ApoorvaSinha
Jul 4, 2022

1.select CONCAT(FIRST_NAME," ",LAST_NAME) as First_Name, salary from employees where salary NOT IN(10000,15000);

2.select CONCAT(FIRST_NAME," ",LAST_NAME) as First_Name,SALARY,department_id FROM EMPLOYEES WHERE salary NOT IN(10000,15000) AND DEPARTMENT_id in (30,100);

3.select CONCAT(FIRST_NAME," ",LAST_NAME) as First_Name, hire_date from employees where YEAR(HIRE_DATE) like "1987%";

4.SELECT first_name FROM employees WHERE first_name LIKE '%b%' AND first_name LIKE '%c%';

5.select LAST_NAME, JOB_ID, SALARY from EMPLOYEES WHERE JOB_ID IN ("IT_PROG", "ST_CLERK") AND SALARY NOT IN(4500,10000,15000);

6.SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE "__e%";

7.SELECT LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE "______";

8.SELECT * FROM EMPLOYEES WHERE LAST_NAME IN ("BLAKE","SCOTT","KING","FORD");

0 replies

Coder-093 · 2022-07-04T15:37:22Z

Coder-093
Jul 4, 2022

select * from employees;

select first_name, last_name, salary from employees where salary not between 10000 and 15000;

select first_name, last_name, salary, department_id from employees where salary not between 10000 and 15000 and department_id in(30,100);

select first_name, last_name, hire_date from employees where year(hire_date) like 1987;

select first_name from employees where first_name like '%b%' and first_name like '%c%';

select * from jobs;

select e.last_name, j.job_title, e.salary from employees e, jobs j where  job_title in ('Programmer', 'Shipping Clerk') and salary not in(4500,10000,15000);

select last_name from employees where last_name like '__e%';

select last_name from employees where last_name like '______';

select * from employees where last_name in ('blake','scott','king','ford');

0 replies

Poojapoo-123 · 2022-07-05T07:13:46Z

Poojapoo-123
Jul 5, 2022

select concat(first_name," ",last_name),salary from employees where salary not between 10000 and 15000;
select concat(first_name," ",last_name),salary from employees where salary not between 10000 and 15000 and department_id in(30,100);
3.select concat(first_name," ",last_name),hire_date from employees where hire_date like '1987%';
select first_name from employees where first_name like '%b%'and '%c%';
select last_name,job_id,salary from employees where job_id in ('IT_PROG','SH_CLERK') and salary not in(4500,10000,15000);
6.select last_name from employees where last_name like '__e%';
select last_name from employees where last_name like '_';
8.select last_name from employees where last_name like '';
9.select * from employees where last_name in('BLAKE','SCOTT','KING'and 'FORD');

0 replies

Aniruth-raman · 2023-10-03T13:41:23Z

Aniruth-raman
Oct 3, 2023

Hi @akash-coded, since the dumps file is not available, I have written the code to get the data.

To display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000:

SELECT FIRST_NAME, LAST_NAME, SALARY
FROM employees
WHERE salary NOT BETWEEN 10000 AND 15000;

To display the name (first_name, last_name) and salary for all employees whose salary is not in the range $10,000 through $15,000 and are in department 30 or 100:

SELECT FIRST_NAME, LAST_NAME, SALARY
FROM employees
WHERE SALARY NOT BETWEEN 10000 AND 15000
AND DEPARTMENT_ID IN (30, 100);

To display the name (first_name, last_name) and hire date for all employees who were hired in 1987:

SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
FROM employees
WHERE EXTRACT(YEAR FROM HIRE_DATE) = 1987;

To display the first_name of all employees who have both "b" and "c" in their first name:

SELECT FIRST_NAME
FROM employees
WHERE FIRST_NAME LIKE '%b%c%' OR first_name LIKE '%c%b%';

To display the last name, job, and salary for all employees whose job is that of a Programmer or a Shipping Clerk, and whose salary is not equal to $4,500, $10,000, or $15,000:

SELECT e.LAST_NAME, j.JOB_TITLE, e.SALARY
FROM employees e
JOIN jobs j ON e.JOB_ID = j.JOB_ID
WHERE j.JOB_TITLE IN ('Programmer', 'Shipping Clerk')
AND e.SALARY NOT IN (4500, 10000, 15000);

To display the last name of employees having 'e' as the third character:

SELECT LAST_NAME
FROM employees
WHERE LAST_NAME LIKE '__e%';

To display the last name of employees whose names have exactly 6 characters:

SELECT LAST_NAME
FROM employees
WHERE LENGTH(LAST_NAME) = 6;

To select all record from employees where last name in 'BLAKE', 'SCOTT', 'KING' and 'FORD':

SELECT *
FROM employees
WHERE LAST_NAME IN ('BLAKE', 'SCOTT', 'KING', 'FORD');

0 replies

Maha9-lakshmi · 2023-10-04T12:44:50Z

Maha9-lakshmi
Oct 4, 2023

quesion-1

SELECT first_name,last_name,salary FROM employees WHERE salary NOT BETWEEN 10000 AND 15000

question-2

SELECT first_name,last_name,salary FROM employess WHERE salary NOT BETWEEN 10000 AND 15000 AND deparment_id=30 OR 100

question-3

SELECT first_name,last_name FROM employees WHERE hire_date=1987

question-4

SELECT first_name FROM employees WHERE first_name LIKE '%b%'
AND first_name LIKE '%c%';

question-5

SELECT last_name,job and salary FROM employees WHERE job_title IN(Programmer,Shipping Clerk) AND salary NOT IN(4500,10000,150000)

question-6

SELECT last_name FROM employees WHERE last_name like '__e%';

question-7

SELECT last_name FROM employees WHERE last_name LIKE '______';

question-8

SELECT * FROM employees WHERE last_name IN('BLAKE','SCOTT','KING','FORD');

0 replies

Task - DML Commands - MySQL Restricting and Sorting data #17

Uh oh!

akash-coded Jul 1, 2022 Maintainer

Database = hr

Dump file: hr.sql

DB Schema

Queries

Replies: 13 comments

Uh oh!

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

akash-coded
Jul 1, 2022
Maintainer

Database = `hr`