Site Loader

Name:Jonathan Edward H. SilvosaYear: 3rd Year Course: BSITSubject: IT 7 IT 7 – Exercise 01 Table 1. Select Statement Exercise a. Display the first name and age for everyone that’s in the table. select FName, Age from tbl_EmpInfo b. Display the first name, last name, and city for everyone that’s not from Payson. select FName, LName, City from tbl_EmpInfo where City <> ‘Payson’ c. Display all columns for everyone that is over 40 years old. select * from tbl_EmpInfo where Age > 40 d. Display the first and last names for everyone whose last name ends in an “ay”. elect FName, LName from tbl_EmpInfo where LName LIKE ‘%ay’ e. Display all columns for everyone whose first name equals “Mary”. select * from tbl_EmpInfo where FName = ‘Mary’ f. Display all columns for everyone whose first name contains “Mary”. select * from tbl_EmpInfo where FName LIKE ‘%Mary%’ 2. Creating Table Exercise create table tbl_Employee (FName varchar(50), LName varchar(50), Title varchar(50), Age int, Salary decimal(18,2)); 3. Inserting Statement Exercise insert into tbl_Employee (FName, LName, Title, Age, Salary) values (‘Dirk’, ‘Smith’, ‘Programmer II’, 32, 75020); a.

Select all columns for everyone in your employee table. select * from tbl_Employee b. Select all columns for everyone with a salary over 30000. select * from tbl_Employee where Salary > 30000 c. Select first and last names for everyone that’s under 30 years old. select FName, LName from tbl_Employee where Age ;lt; 30 d. Select first name, last name, and salary for anyone with “Programmer” in their title. select FName, LName, Salary from tbl_Employee where Title = ‘Programmer’ e. Select all columns for everyone whose last name contains “ebe”. select * from tbl_Employee where LName LIKE ‘%ebe%’ f.

Select the first name for everyone whose first name equals “Potsy”. select FName from tbl_Employee where FName LIKE ‘%Potsy%’ g. Select all columns for everyone over 80 years old. select * from tbl_Employee where Age ;gt; 80 h. Select all columns for everyone whose last name ends in “ith”. select * from tbl_Employee where LName LIKE ‘%ith’ 4. Update Statement Exercise a. Jonie Weber just got married to Bob Williams. She has requested that her last name be updated to Weber-Williams. update tbl_Employee set LName = ‘Weber-Williams’ where LName = ‘Weber’ and FName = ‘Jonie’ b. Dirk Smith’s birthday is today, add 1 to his age. pdate tbl_Employee set Age = Age+1 where FName = ‘Dirk’ and LName = ‘Smith’ c. All secretaries are now called “Administrative Assistant”. Update all titles accordingly. update tbl_Employee set Title = ‘Administrative Assistant’ where Title = ‘Secretary’

d. Everyone that’s making under 30000 are to receive a 3500 a year raise. update tbl_Employee set Salary = Salary+3500 where Salary ;lt; 30000 e. Everyone that’s making over 33500 are to receive a 4500 a year raise. update tbl_Employee set Salary = Salary+4500 where Salary > 33500 f. All “Programmer II” titles are now promoted to “Programmer III”. pdate tbl_Employee set Title = ‘Programmer III’ where Title = ‘Programmer II’ g. All “Programmer” titles are now promoted to “Programmer II”. update tbl_Employee set Title = ‘Programmer II’ where Title = ‘Programmer’ 5. Delete Statement Exercise a. Jonie Weber-Williams just quit, remove her record from the table. delete from tbl_Employee where FName = ‘Jonie’ and LName = ‘Weber-Williams’ b. It’s time for budget cuts. Remove all employees who are making over 70000 dollars. delete from tbl_Employee where Salary ;gt;70000 6. Drop Table Exercise a. Drop your employee table. drop table tbl_Employee

Post Author: admin

Leave a Reply

Your email address will not be published. Required fields are marked *