SQL Queries for Practice | SQL Queries

1. Answer the following:

(i) Write a query to create a table.

(ii) Write the query to insert record into a table.

(iii) Write the query to delete a record from a table.

(iv) Write the query to update a record in a table.

(v) Write the query to delete a table.

(vi) Write the query to select all records from a table to retrieve data.

(vii) Write the query to select the specific column from a table.

(viii) Write the query to select distinct values (unique values) from a column.

(ix) Write the query to filter records using a where clause.

(x) Write the query to sort values in column in ascending order.

(xi) Write the query to sort values in column in descending order.

(xii) Write the query to count the number of rows in a table.

(xiii) Write the query to find the sum of values in a column.

(xiv) Write the query to find the average value of a column.

(xv) Write the query to get the minimum value from a column.

(xvi) Write the query to get the minimum value from a column.

2. Answer the Following:

(i) Write the query to create a table named ‘Employees’ with fields empID, empName, empAge, empAddress and empSalary.

(ii) Write the query to insert values in the ‘Employees’ table.

(iii) Consider the ‘Employees’ table and write the query to update the age of empID=1 in the ‘Employees’ table.

(iv) Consider the ‘Employees’ table and write the query to delete record of empID=3 from the ‘Employees’ table.

(v) Consider the Employees table and write the query to select all records from the ‘Employees’ table.

(vi) Consider the Employees table and write the query to select the ‘empName’ and ‘empAddress’ from the ‘Employees’ table.

(vii) Consider the ‘Employees’ table and write the query to select distinct values from the ‘empAddress’ column in the ‘Employees’ table.

(viii) Consider the ‘Employees’ table and write the query to filter and select those records where empAddress=’Tinsukia’.

(ix) Consider the ‘Employees’ table and write the query to sort name of employees of ‘Employees’ table in ascending order.

(x) Consider the ‘Employees’ table and write the query to sort name of employees of ‘Employees’ table in descending order.

(xi) Consider the ‘Employees’ table and write the query to count the number of rows in the ‘Employees’ table.

(xii) Consider the ‘Employees’ table and write the query to retrieve all employees whose salary is between 30000 and 50000 in the ‘Employees’ table.

(xiii) Consider the ‘Employees’ table and write the query to add another column called ‘Department’.

3. Answer the following:

(i) Create a table DEPARTMENT which has the attributes Dept_ID, DeptName, Address, PhNo.

(ii) Add one column Email of datatype VARCHAR and size 30.

(iii) Insert few records into the table.

(iv) Create a query to display all the data from the DEPARTMENT table.

(v) Update the record where DeptID is 9.

(vi) Delete a record from the table.

4. Answer the following:

(i) Create table Locations (LocationID, City, State, Country).

(ii) Insert 5 records into the Location table.

(iii) Create a query to display all the records from the Locations table.

(iv) Display City and State field data from the table.

(v) Rename the table Locations to Address.

5. Answer the following:

(i) Consider a table called ‘products’ with fields such as product_id, product_name, product_quantity, product_price.

(ii) Write the query to select all products names whose product price is greater than 500.

(iii) Write the query to calculate the sum of values in the product_price column in the ‘products’ table.

(iv) Write the query to calculate the average value of the product_price column in the ‘products’ table.

(v) Write the query to get the minimum value from the product price column in the products table.

(vi) Write the query to get the maximum value from the product price column in the products table.

6. Answer the following:

(i) Create a table Customer which has the fields Cust_No, Cust_Name, Birth_date, City, State, Pincode, Ph_No.

(ii) Insert few records into the Customer table.

(iii) Create a query to display all the data from the Customer table.

(iv) Add one column Email of data type VARCHAR and size 30 to the table Customer.

(v) Create a query to display unique Pincode from the Customer table.

(vi) Display records of Customer from Tinsukia and Assam.

(vii) Display records of Customer from Tinsukia or Dibrugarh.

(viii) Display the customer records in the alphabetic order of state.

(ix) Retrieve all the records of customer table except Cust_No 1005.

(x) Create a query to drop the table Customer

7. Answer the following:

(i) Create a table Employee which has the fields EmpID, EmpName, EmpAddress, EmpPhone, HireDate, EmpSalary.

(ii) Add one column Email of data type VARCHAR and size 30.

(iii) Insert records into the Employee table.

(iv) Create a query to display the employee name and salary of employees earning more than 12,000.

(v) Display the employee name and salary for all employees whose salary is not in the range of 5,000 and 12,000.

(vi) Display the employee name and hire date of employees who is hired between February 20, 2020, and March 31, 2024.

(vii) Display the name of the employees and hire date of every employee who was hired in 2023.

(viii) Retrieve records of Employee  table where the employee name begins with A.

(ix) Rename the table Employee to EMP.

(x) Drop the table EMP.

8. Create a table “superhero” which has the attributes: id, fname, lname, dob, designation, state, country, salary.

(i) Insert five records in the table.

(ii) Write a query to insert data (0001, Chris, Evans, 13/06/1981, actor, California, USA, 7000000) to superhero table.

(iii) Write a query to update the country “USA” to “America”.

(iv) Write a query to find the highest salary.

(v) Write a query to delete a single row of data from the superhero table.

9. Write SQL query to create a table employeeinfo including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion.

(i) Sets constraints on certain columns like NOT NULL, DEFAULT, NULL, and defines a primary key on ‘EMPLOYEE_ID’.

ANSWERS-1

(i) create table tableName(columnName1 datatype(width), columnName2 datatype(width), columnName3 datatype(width), columnName4 datatype(width));

Example: create table student (rollNo int(4), name varchar(10), address varchar(15), dob date);

(ii) insert into tableName values(value1,value2,value3,value4);

Example:

or

insert into tableName(column1,column2,column3,column4) values(value1,value2,value3,value4);

Example:

(iii) delete from tableName where condition;

Example: delete from student where rollNo=4;

(iv) update tableName set columnName=newValue where condition;

Example: update student set address=’Tinsukia’ where rollNo=1;

(v) drop table tableName;

Example: drop table employee;

(vi) select * from tableName;

Example: select * from student;

(vii) select column1, column2 from tableName;

Example: select rollNo, name from student;

(viii) select distinct columnName from tableName;

Example: select distinct address from student;

(ix) select * from tableName where condition;

Example: select * from student where address=’Tinsukia’;

(x) select * from tableName order by columnName;

Example: select * from student order by name;

(xi) select * from tableName order by columnName desc;

Example: select * from student order by name desc;