xi) Display name, department and date of birth of those employees whose date of birth is before a particular date. Create a database in your name and do the following. 1. Create table MyFriends (Friend Id int), (Name varchar(20)),(Age int), (Salary dec(9,2)),(D_birth date). Enter 10 records. (i) Display all rows. (ii) Insert a new column place(varchar) Supply values for the newly added column and Display all rows. Display Sum, maximum, minimum and average values of salary. Delete rows where place contains the letter 'M' and display the remaining rows. (iv)

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question
100%

kindly answer  only the  questions marked in blue ink .The question is  base d in mysql

10:31
25
Q.2 Write a query in MYSQL to do
i) Display structure of the table.
ii) Display all the records
ii) Change name of the column DEPARTMENT_ID to DPT_ID and name of the
column last_name to l_name. Check the new titles.
iv) Delete the details of employee whose id is 143. Check the change.
Change MANAGER_ID of an employee whose last name is Higgins, to 124.
vi) Display distinct JOB_ID
vii) Display the last name and salary for all employees whose salary is not in the
range of 5000 and 12000.
viii) Change name of the column hire date to startdate. Check the change.
ix) Display the last name, job ID and start date of employees hired between
February 20,1999 and May 31,1999. Order the query result in ascending
order by start date.
Display first name and startdate of every employee who was hired in 1994.
xi) Display the last name and job title of all employees who do not have
manager.
xii) Display the last name of all employees who have an 'a' and an 'e'in their last
name.
xiii) Display the last name of all employees who have an 'a' or 'e' in their last
name. Sort the records in alphabetical orders of last name.
kinCreate a new column commission and enter commission as 20% for Eleni and
Jonathan and as 15% for Kimberely.
xv) For each employee, display the employee number, last name, salary and
salary increased by 15%. Label the column as New salary.
xvi) Create a query that displays the employee's last name and salary. Sort the
date in descending of salary.
Transcribed Image Text:10:31 25 Q.2 Write a query in MYSQL to do i) Display structure of the table. ii) Display all the records ii) Change name of the column DEPARTMENT_ID to DPT_ID and name of the column last_name to l_name. Check the new titles. iv) Delete the details of employee whose id is 143. Check the change. Change MANAGER_ID of an employee whose last name is Higgins, to 124. vi) Display distinct JOB_ID vii) Display the last name and salary for all employees whose salary is not in the range of 5000 and 12000. viii) Change name of the column hire date to startdate. Check the change. ix) Display the last name, job ID and start date of employees hired between February 20,1999 and May 31,1999. Order the query result in ascending order by start date. Display first name and startdate of every employee who was hired in 1994. xi) Display the last name and job title of all employees who do not have manager. xii) Display the last name of all employees who have an 'a' and an 'e'in their last name. xiii) Display the last name of all employees who have an 'a' or 'e' in their last name. Sort the records in alphabetical orders of last name. kinCreate a new column commission and enter commission as 20% for Eleni and Jonathan and as 15% for Kimberely. xv) For each employee, display the employee number, last name, salary and salary increased by 15%. Label the column as New salary. xvi) Create a query that displays the employee's last name and salary. Sort the date in descending of salary.
MYSQL-2
Sem - V
B.Sc.
Practical No.2
a) Create a table with the following columns. (Give name of your
choice)
Name of employee ( NAME, variable character)
Date of birth (DOB, date).
Salary of employee (SALARY, integer).
Department (DEPT ,variable character).
[Department: SALE, ACCT, MARKETING]
Enter at least 10 records.
b) Give SQL queries for the following
Give details of all employees in the company.
(i)
(ii)
List names of employees along with their salaries.
(iii)
(iv)
Give details of employees from SALE department.
Give details of employees with salary between 5000 and
10000.
(v)
Add two more records.
(vi)
Give name, date of birth and salary of employees with
department ACCT and salary more than 20000.
List the names & date of birth of employee whose name
(vi)
starts with 'B'.
List names of all employees whose salary is greater than
Display the names of distinct departments.
List names of all the employees with first three
characters unknown and ending with NI.
Display name, department and date of birth of those employees
whose date of birth is before a particular date.
(vii)
30000.
(ix)
(x)
xi)
Create a database in your name and do the following.
1. Create table MyFriends (Friend Id int), (Name varchar(20)),(Age int),
(Salary dec(9,2)),(D_birth date). Enter 10 records.
Display all rows.
Insert a new column place(varchar)
Supply values for the newly added column and Display all rows.
Display Sum, maximum, minimum and average values of salary.
Delete rows where place contains the letter 'M' and display the
remaining rows.
Delete the table MyFriend from the database.
(i)
(ii)
(iv)
(vi)
Transcribed Image Text:MYSQL-2 Sem - V B.Sc. Practical No.2 a) Create a table with the following columns. (Give name of your choice) Name of employee ( NAME, variable character) Date of birth (DOB, date). Salary of employee (SALARY, integer). Department (DEPT ,variable character). [Department: SALE, ACCT, MARKETING] Enter at least 10 records. b) Give SQL queries for the following Give details of all employees in the company. (i) (ii) List names of employees along with their salaries. (iii) (iv) Give details of employees from SALE department. Give details of employees with salary between 5000 and 10000. (v) Add two more records. (vi) Give name, date of birth and salary of employees with department ACCT and salary more than 20000. List the names & date of birth of employee whose name (vi) starts with 'B'. List names of all employees whose salary is greater than Display the names of distinct departments. List names of all the employees with first three characters unknown and ending with NI. Display name, department and date of birth of those employees whose date of birth is before a particular date. (vii) 30000. (ix) (x) xi) Create a database in your name and do the following. 1. Create table MyFriends (Friend Id int), (Name varchar(20)),(Age int), (Salary dec(9,2)),(D_birth date). Enter 10 records. Display all rows. Insert a new column place(varchar) Supply values for the newly added column and Display all rows. Display Sum, maximum, minimum and average values of salary. Delete rows where place contains the letter 'M' and display the remaining rows. Delete the table MyFriend from the database. (i) (ii) (iv) (vi)
Expert Solution
steps

Step by step

Solved in 3 steps

Blurred answer
Knowledge Booster
Fundamentals of Boolean Algebra and Digital Logics
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education