SQL statements

Question 1

1.Create a new database called ADTelecom.

Answer 1

CREATE DATABASE ADTelecom;

Question 2

2.Use the database that you have created (ADTelecom).

Answer 2

USE ADTelecom;

Question 3

3.Create a table in the database named Companystaff.

Answer 3

CREATE TABLE Companystaff (
CustomerID varchar(60),
FirstName varchar(60),
LastName varchar(60),
City varchar(255),
Sex char(1),
Department varchar(60),
Salary int
);

Question 4

4.Show the table created within the database.

Answer 4

SHOW TABLES;

Question 5

5.Display the structure of the table Companystaff.

Answer 5

DESCRIBE Companystaff;

Question 6

6.Populate the table Companystaff by inserting the values listed below.

Answer 6

INSERT INTO Companystaff
VALUES (‘86-192’, ‘John’, ‘Hayes’, ‘Palo Alto’, ‘M’, ‘Operations’, 127000),
(‘28-019’, ‘Elizabeth’, ‘McFarlane’, ‘Rye’, ‘F’, ‘Support’, 73000),
(‘89-677’, ‘Kenneth’, ‘Johnson’, ‘Stamford’, ‘M’, ‘Analytics’, 97000),
(‘73-128’, ‘Michael’, ‘Smith’, ‘Harrison’, ‘M’, ‘Analytics’, 94000), (‘12-321’, ‘William’, ‘Hayes’, ‘Pittsfield’, ‘M’, ‘Sales’, 105000),
(‘66-336’, ‘Rose’, ‘Turner’, ‘Harrison’, ‘F’, ‘Operations’, 115000),
(‘28-019’, ‘Judy’, ‘Lindsay’, ‘Rye’, ‘F’, ‘Support’, 68000);

Question 7

7.Retrieve all the records from the Companystaff table.

Answer 7

SELECT * FROM Companystaff;

Question 8

8.Retrieve all the values for columns FirstName, LastName, and Salary from Companystaff table.

Answer 8

SELECT FirstName, LastName, Salary FROM Companystaff;

Question 9

9.Retrieve a record from the Companystaff table whose last name is ‘Hayes’.

Answer 9

SELECT * FROM Companystaff WHERE LastName=’Hayes’;

Question 10

10.Retrieve Firstname, Lastname, Department from the Companystaff table with Department ‘Operations’.

Answer 10

SELECT FirstName, LastName, Department FROM Companystaff WHERE Department=’Operations’;

Question 11

11.Retrieve Firstname, Lastname, Sex, Salary from the Companystaff table with Sex ‘M’ AND Salary greater than 100000.

Answer 11

SELECT FirstName, LastName, Sex, Salary FROM Companystaff WHERE Sex=’M’ AND Salary>100000;

Question 12

12.Retrieve Firstname, Lastname, City, Salary from the Companystaff table with City ‘Harrison’ OR Salary less than or equal to 105000.

Answer 12

SELECT FirstName, LastName, City, Salary FROM Companystaff WHERE City=’Harrison’ OR Salary<=105000;

Question 13

13.Retrieve Firstname, Lastname, Salary from the Companystaff table and store the result in a new column called IncreasedSalary when each salary is added with 5000.

Answer 13

SELECT FirstName, LastName, Salary, (Salary + 5000) AS IncreasedSalary FROM Companystaff;

Question 14

14.Display all records from the Companystaff table in ascending order by Lastname.

Answer 14

SELECT * FROM Companystaff ORDER BY LastName ASC;

Question 15

15.Display all the records from Companystaff in descending order by Salary.

Answer 15

SELECT * FROM Companystaff ORDER BY Salary DESC;