MySQL CREATE TABLE students ( id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), age INT, major VARCHAR(50), faculty VARCHAR(50) ); CREATE TABLE location ( id INT PRIMARY KEY, name VARCHAR(50), rooms INT ); CREATE TABLE faculty ( id INT PRIMARY KEY, name VARCHAR(50), department_id INT ); 4. Find the number of employees in each department who get no commission or have salary less than 5000. 5. Find the maximum salary of employees in each department that the employee was hired 15 years before now. *hint: user TIMESTAMPDIFF(,,), the unit can be YEAR, MONTH, DAY, HOUR, etc... 6. Find the last name of all employees that were not hired on Tuesday *hint: (Use DATE_FORMAT() function amd the format you need is %W, also use UPPER()
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
MySQL
CREATE TABLE students (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT,
major VARCHAR(50),
faculty VARCHAR(50)
);
CREATE TABLE location (
id INT PRIMARY KEY,
name VARCHAR(50),
rooms INT
);
CREATE TABLE faculty (
id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT
);
4. Find the number of employees in each department who get no commission or have salary less than
5000.
5. Find the maximum salary of employees in each department that the employee was hired 15 years
before now. *hint: user TIMESTAMPDIFF(<unit type>,<Date_value 1>,<Date_value 2>), the unit
can be YEAR, MONTH, DAY, HOUR, etc...
6. Find the last name of all employees that were not hired on Tuesday *hint: (Use
DATE_FORMAT() function amd the format you need is %W, also use UPPER()
7. Find the number of employees in each department who have a manager.
8. Find the number of employees for each manager whose employees' minimum salary is greater
than 5000.
9. Find the number of employees for each job and department. In the output list the number that you
have found together with the job id and department id.
10. Find the number of students for each faculty, but list only the faculties that have more than one
student.
11. Find the average, highest, and lowest age for students.
12. Count number of faculty members by each department.
13. Display average employee salary by department, but don't include departments with an average
salary of less than $75,000.
Trending now
This is a popular solution!
Step by step
Solved in 4 steps
