Skip to content

Latest commit

 

History

History
249 lines (188 loc) · 6.77 KB

README.md

File metadata and controls

249 lines (188 loc) · 6.77 KB
Raw

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

  1. From the following table return complete information about the employees.
SELECT * FROM Employee;
  1. From the following table, write a SQL query to find the cities of all employees. Return city.
SELECT City FROM Employee;
  1. From the following table, write a SQL query to find the unique addressline of the employees. Return addressline.
SELECT DISTINCT AddressLine 
FROM Employee;
  1. From the following table, write a SQL query to return EmployeeID, FirstName, LastName, City and AddressLine.
SELECT EmployeeID,
       FirstName,
       LastName,
       City,
       AddressLine
FROM Employee;
  1. 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;
  1. 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;
  1. From the following table, write a SQL query to find the EmployeeID, FirstName, Email of all the employees.
SELECT EmployeeID,
       FirstName,
       Email
FROM Employee;
  1. 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;
  1. 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');
  1. 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');
  1. 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');
  1. 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');
  1. 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');
  1. 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');
  1. 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');
  1. From the following table, write a SQL query to find the details of the employee ‘Carol’.
SELECT *
FROM Employee
WHERE FirstName = 'Carol';
  1. 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;
  1. From the following table, write a SQL query to find the details of the employee LastName ‘Santos’.
SELECT *
FROM Employee
WHERE LastName = 'Santos';
  1. 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';
  1. 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';
  1. 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');
  1. 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');
  1. 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%';
  1. 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';
  1. 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.