Description
Write mysql queries for given labsheetNeed commandline screenshots
Unformatted Attachment Preview
ICT1222
Practical 03
1. Write a SQL statement to create a simple table city including columns
city_id,city_name and region_id.
2. Write a SQL statement to create a simple table city including columns
city_id,city_name and region_id which is already exists.
3. Write a SQL statement to create a table city set a constraint NULL.
4. Write a SQL statement to create a table named jobs including columns job_id, job_title,
min_salary, max_salary and check whether the max_salary amount exceeding the upper
limit 25000.
5. Write a SQL statement to create a table named city including columns
city_id,city_name and region_id and make sure that no city except Colombo, Galle and
Matar will be entered in the table.
6. Write a SQL statement to create a table named job_history including columns
employee_id, start_date, end_date, job_id and department_id and make sure that the
value against column end_date will be entered at the time of insertion to the format like
‘–/–/—-‘.
7. Write a SQL statement to create a table named city including columns
city_id,city_name and region_id and make sure that no duplicate data against column
city_id will be allowed at the time of insertion.
8. Write a SQL statement to create a table named jobs including columns job_id, job_title,
min_salary and max_salary, and make sure that, the default value for job_title is blank
and min_salary is 8000 and max_salary is NULL will be entered automatically at the
time of insertion if no value assigned for the specified columns.
9. Write a SQL statement to create a table named city including columns
city_id,city_name and region_id and make sure that the city_id column will be a key
field which will not contain any duplicate data at the time of insertion.
10. Write a SQL statement to create a table countries including columns city_id,city_name
and region_id and make sure that the combination of columns city_id and region_id
will be unique.
11. Write a SQL statement to create a table job_history including columns employee_id,
start_date, end_date, job_id and department_id and make sure that, the employee_id
column does not contain any duplicate value at the time of insertion and the foreign key
column job_id contain only those values which are exists in the jobs table.
12. Write a SQL statement to create a table employees 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
1
ICT1222
Practical 03
contain any duplicate value at the time of insertion and the foreign key columns
combined by department_id and manager_id columns contain only those unique
combination values, which combinations are exists in the departments table.
13. Create the following table.
14. Write a query to fetch the EmpFname from the EmployeeInfo table.
15. Write a query to get the current date.
16. Write a query to retrieve the first four characters of EmpLname from the
EmployeeInfo table.
17. Write a query to fetch only the place name(string before brackets) from the Address
column of EmployeeInfo table.
18. Write a query to find the names of employees that begin with ‘S’.
19. Write a query to fetch all the records from the EmployeeInfo table ordered by
EmpLname in descending order and Department in the ascending order.
20. Write a query to fetch details of employees whose EmpLname ends with an alphabet
‘A’ and contains five alphabets.
21. Write a query to fetch details of all employees excluding the employees with first
names, “Sanjay” and “Sonia” from the EmployeeInfo table.
2
ICT1222
Practical 03
22. Write a query to fetch details of employees with the address as “DELHI(DEL)”.
23. Write a query find number of employees whose DOB is between 02/05/1970 to
31/12/1975 and are grouped according to gender.
3
Purchase answer to see full
attachment
