SQL Intermediate Practice — Users Table
Topics: SELECT, WHERE, AND/OR/NOT, BETWEEN/IN, ORDER BY, LIMIT, UPDATE, DELETE, ALTER
Table structure
Users(Id PRIMARY KEY, Name, email UNIQUE NOT NULL, Area, Salary, Designation)
Use the following table definition and the 50-row dataset from your own practice database. The questions are designed to be solved
against Users.
Suggested setup:
CREATE TABLE Users (
Id INT PRIMARY KEY,
Name VARCHAR(100),
email VARCHAR(255) UNIQUE NOT NULL,
Area VARCHAR(100),
Salary INT,
Designation VARCHAR(100)
);
Tip: For UPDATE/DELETE/ALTER questions, run them only after taking a backup or working on a practice copy of the table.
1. SELECT Query
1. Display the Name and Designation of every user.
2. Display all columns for users whose Designation is 'Data Analyst'.
3. Display Name, Area, and Salary for every user.
4. Display the Id, Name, and email columns only.
5. Display each user's Name, Salary, and Designation sorted as stored in the table.
6. Display the names and salaries of users earning more than 80000.
7. Display the names and areas of users from Mumbai.
8. Display Name, email, and Designation for users working in a Data/technology role (Data Analyst, Data Scientist, or Data
Engineer).
9. Display all details of the user whose Id is 25.
10. Display Name, Area, Salary, and Designation for all users.
2. WHERE Conditions
1. Find users whose Salary is greater than 75000.
2. Find users whose Salary is less than 60000.
3. Find users whose Area is 'Pune'.
4. Find users whose Designation is 'SQL Developer'.
5. Find users whose Name is 'Riya Chatterjee'.
6. Find users whose email is '[Link]@[Link]'.
7. Find users whose Area is not 'Mumbai'.
8. Find users whose Designation is not 'Business Analyst'.
9. Find users whose Salary is exactly 68000.
10. Find users whose Id is greater than 40.
3. AND / OR / NOT
1. Find users from Mumbai AND earning more than 70000.
2. Find users from Pune AND working as a SQL Developer.
3. Find users earning more than 80000 AND working as a Data Scientist.
4. Find users from Mumbai OR Pune.
5. Find users working as either a Data Analyst OR Business Analyst.
6. Find users whose Salary is below 60000 OR above 90000.
7. Find users who are NOT from Delhi.
8. Find users who are NOT SQL Developers AND earn more than 70000.
9. Find users from Mumbai AND (Data Analyst OR Data Scientist).
10. Find users from Pune OR Bengaluru AND earning at least 80000; use parentheses so the intended logic is explicit.
4. BETWEEN / IN
1. Find users whose Salary is BETWEEN 60000 AND 80000.
2. Find users whose Salary is BETWEEN 70000 AND 90000.
3. Find users whose Id is BETWEEN 10 AND 20.
4. Find users whose Area is IN ('Mumbai','Pune','Bengaluru').
5. Find users whose Designation is IN ('Data Analyst','Data Scientist').
6. Find users whose Salary is NOT BETWEEN 60000 AND 70000.
7. Find users whose Area is NOT IN ('Delhi','Mumbai').
8. Find users whose Id is IN (5, 15, 25, 35, 45).
9. Find users whose Designation is IN ('SQL Developer','Database Administrator','Data Engineer').
10. Find users whose Salary is BETWEEN 80000 AND 95000 AND Area is IN ('Mumbai','Hyderabad','Bengaluru').
5. ORDER BY
1. Display all users ordered by Salary from highest to lowest.
2. Display all users ordered by Salary from lowest to highest.
3. Display users ordered alphabetically by Name.
4. Display users ordered by Area ascending and Salary descending.
5. Display users ordered by Designation ascending and Name ascending.
6. Display only Name, Salary, and Area ordered by Salary descending.
7. Display users from Mumbai ordered by Salary descending.
8. Display users ordered by Salary descending, then Name ascending for equal salaries.
9. Display users ordered by Id descending.
10. Display users ordered by Area ascending, then Designation ascending, then Salary descending.
6. LIMIT
1. Display the 5 highest-paid users.
2. Display the 10 lowest-paid users.
3. Display the first 10 users when ordered by Name alphabetically.
4. Display the top 5 Data Analysts by Salary.
5. Display the 3 highest-paid users from Mumbai.
6. Display the 5 lowest-paid SQL Developers.
7. Display 10 users from Bengaluru ordered by Salary descending.
8. Display the 5 users with the highest Id values.
9. Display the 7 users from all areas whose Salary is between 70000 and 90000, ordered by Salary descending.
10. Skip the first 10 highest-paid users and display the next 5 highest-paid users (using LIMIT with OFFSET).
7. UPDATE
1. Increase the Salary of user Id 1 by 5000.
2. Change the Area of user Id 2 to 'Mumbai'.
3. Change the Designation of user Id 3 to 'Senior Software Engineer'.
4. Increase the Salary by 10% for all Data Analysts.
5. Increase the Salary by 5000 for users from Pune.
6. Change the Area to 'Bengaluru' for users whose Designation is 'Data Engineer' and Area is 'Hyderabad'.
7. Set Salary to 60000 for SQL Developers earning less than 60000.
8. Increase Salary by 8% for users earning below 65000.
9. Change the Designation to 'Senior Data Analyst' for Data Analysts earning more than 85000.
10. Change the Area to 'Mumbai' for user Id 49.
8. DELETE
1. Delete the user whose Id is 50.
2. Delete users whose Salary is below 55000.
3. Delete users from the Area 'Goa'.
4. Delete users whose Designation is 'Database Administrator'.
5. Delete users whose Salary is greater than 95000.
6. Delete the user whose email is '[Link]@[Link]'.
7. Delete users from Patna or Bhopal.
8. Delete SQL Developers earning less than 60000.
9. Delete users whose Id is between 45 and 50.
10. Delete users who are from Mumbai and earn less than 60000.
9. ALTER
1. Add a new column Age of data type INT to the Users table.
2. Add a new column Phone of data type VARCHAR(15).
3. Rename the column Area to City.
4. Change the data type of Salary to DECIMAL(10,2).
5. Increase the maximum length of Name from VARCHAR(100) to VARCHAR(150).
6. Rename the column Designation to Job_Title.
7. Drop the Phone column.
8. Add a column Joining_Date of data type DATE.
9. Rename the table Users to Company_Users.
10. Add a column Department of data type VARCHAR(100).