MYSQL ASSIGNMENT

CREATE DATABASE

CREATE DATABASE testdb;

SHOW EXISTING DATABASE

SHOW CREATE DATABASE testdb;

USE COMMAND

USE testdb;

CREATE TABLE EMPLOYEE

CREATE TABLE Employee  
(  
EmployeeID int,  
FirstName varchar(255),  
LastName varchar(255),  
Email varchar(255),  
AddressLine varchar(255),  
City varchar(255)  
);

INSERT RECORDS IN EMPLOYEE TABLE

INSERT INTO Employee (EmployeeID, FirstName, LastName, Email, AddressLine, City)
VALUES ('101', 'Lucas', 'Santos', 'lucassantos@gmail.com', 'Brazil', 'Sao Paulo'),
('102', 'Carlos', 'Santiago', 'carlossantiago@gmail.com', 'Argentina', 'Buenos Aires'),
('103', 'Emanuel', 'DaSilva', 'emanueldasilva@gmail.com', 'Brazil', 'Rio Grande do Sul'),
('104', 'Abril', 'Rodriguez', 'abrilrodrigues@gmail.com', 'Argentina', 'Mendoza'),
('105', 'Carolina', 'Bentresca', 'carolinabentresca@gmail.com', 'Chile', 'Concepsion'),
('106', 'Carol', 'Santos', 'carolsantos@gmail.com', 'Chile', 'Santiago'),
('107', 'Gabriela', 'Lopez', 'Gabrielalopez@gmail.com', 'Brazil', 'Amazonas'),
('108', 'Michael', 'DeCarvalho', 'michaeldecarvalho@gmail.com', 'Brazil', 'Fortaleza'),
('109', 'George', 'Spencer', 'georgespencer@gmail.com', 'United Kingdom', 'London'),
('110', 'Christina', 'Diemert', 'christinadiemert@gmail.com', 'United States', 'California');

QUERIES

From the following table return complete information about the employees.

SELECT * FROM Employee;

From the following table, write a SQL query to find the cities of all employees. Return city.

SELECT City FROM Employee;

From the following table, write a SQL query to find the unique addressline of the employees. Return addressline.

SELECT DISTINCT AddressLine 
FROM Employee;

From the following table, write a SQL query to return EmployeeID, FirstName, LastName, City and AddressLine.

SELECT EmployeeID,
       FirstName,
       LastName,
       City,
       AddressLine
FROM Employee;

From the following table, write a SQL query to count the number of characters except the spaces for each FirstName. Return FirstName length.

SELECT length(trim(FirstName))
FROM Employee;

From the following table, write a SQL query to count the number of characters except the spaces for each LastName. Return LastName length.

SELECT length(trim(LastName))
FROM Employee;

From the following table, write a SQL query to find the EmployeeID, FirstName, Email of all the employees.

SELECT EmployeeID,
       FirstName,
       Email
FROM Employee;

From the following table, write a SQL query to find the unique AddressLine with LastName. Return AddressLine and LastName.

SELECT DISTINCT AddressLine, LastName
FROM Employee;

From the following table, write a SQL query to find those employees who do not belong to AddressLine Brazil. Return complete information about the employees.

SELECT *
FROM Employee
WHERE AddressLine NOT IN ('Brazil');

From the following table, write a SQL query to find those employees who do not belong to AddressLine Argentina. Return complete information about the employees.

SELECT *
FROM Employee
WHERE AddressLine NOT IN ('Argentina');

From the following table, write a SQL query to find those employees who EmployeeID's are before 105. Return complete information about the employees.

SELECT *
FROM Employee
WHERE EmployeeID<('105');

From the following table, write a SQL query to find those employees who EmployeeID's are after 105. Return complete information about the employees.

SELECT *
FROM Employee
WHERE EmployeeID>('105');

From the following table, write a SQL query to find those employees who EmployeeID's are before or equal to 105. Return complete information about the employees.

SELECT *
FROM Employee
WHERE EmployeeID<=('105');

From the following table, write a SQL query to find those employees who EmployeeID's are after or equal to 105. Return complete information about the employees.

SELECT *
FROM Employee
WHERE EmployeeID>=('105');

From the following table, write a SQL query to find those employees who EmployeeID is equal to 105. Return complete information about the employees.

SELECT *
FROM Employee
WHERE EmployeeID=('105');

From the following table, write a SQL query to find the details of the employee ‘Carol’.

SELECT *
FROM Employee
WHERE FirstName = 'Carol';

From the following table, write a SQL query to find the FirstName of the employees whose length is six. Return employee FirstName.

SELECT FirstName
FROM Employee
WHERE length(FirstName)=6;

From the following table, write a SQL query to find the details of the employee LastName ‘Santos’.

SELECT *
FROM Employee
WHERE LastName = 'Santos';

From the following table, write a SQL query to find those employees whose AddressLine is ‘Brazil’. Return complete information about the employees.

SELECT *
FROM Employee
WHERE AddressLine = 'Brazil';

From the following table, write a SQL query to find those employees whose AddressLine is ‘Argentina’. Return complete information about the employees.

SELECT *
FROM Employee
WHERE AddressLine = 'Argentina';

From the following table, write a SQL query to find those employees whose AddressLine are either Brazil or Argentina. Return complete information about the employees.

SELECT *
FROM Employee
WHERE AddressLine IN ('Brazil','Argentina');

From the following table, write a SQL query to find those employees whose AddressLine are either Chile or United States. Return complete information about the employees.

SELECT *
FROM Employee
WHERE AddressLine IN ('Chile','United States');

From the following table, write a SQL query to find those employees whose AddressLine begin's with C. Return complete information about the employees.

SELECT *
FROM Employee
WHERE AddressLine LIKE 'C%';

From the following table, write a SQL query to find those employees whose AddressLine ends with l. Return complete information about the employees.

SELECT *
FROM Employee
WHERE AddressLine LIKE '%l';

From the following table, write a SQL query to find those employees whose AddressLine values with 'rg' in between. Return complete information about the employees.

SELECT *
FROM Employee
WHERE AddressLine LIKE '%rg%';

LICENSE

This assignment is under MIT license.

License

ValentineFernandes/MySQL-Assignment

ErrorLooks like something went wrong!

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Error
Looks like something went wrong!

Packages