SQL Part 3: Create new table, Alias, Merge columns

Query to Create EmployeeDetail table:

CREATE TABLE EmployeeDetail


EmpId integer PRIMARY KEY, 

FirstName varchar(255),

LastName varchar(255),

Salary integer,

JoiningDate date, 

Department varchar(255),

Gender varchar(255) 


INSERT INTO EmployeeDetail VALUES(1,'Tom','Garcia',60000.00,'2008-11-10 13:23:44','IT','Male');

INSERT INTO EmployeeDetail VALUES(2,'Lucy','Martin',70000.00,'2009-11-09 13:23:44','HR','FeMale');

INSERT INTO EmployeeDetail VALUES(3,'Frank','Clark',75000.00,'2012-01-24 13:23:44','Admin','Male');

INSERT INTO EmployeeDetail VALUES(4,'Jane','Joseph',78000.00,'2017-01-28 13:23:44','ICT','FeMale');

INSERT INTO EmployeeDetail VALUES(5,'Robert','Hansen',100000.00,'2012-01-29 13:23:44','HR','Male');

Query to Create ProjectDetail table:

CREATE TABLE ProjectDetail


ProjectDetailId integer PRIMARY KEY, 

EmpId integer REFERENCES EmployeeDetail(EmpId), 

ProjectName varchar(255) 


INSERT INTO ProjectDetail VALUES(1,1,'ABC');

INSERT INTO ProjectDetail VALUES(2,1,'DDC');

INSERT INTO ProjectDetail VALUES(3,1,'GHI');

INSERT INTO ProjectDetail VALUES(4,2,'HGT');

INSERT INTO ProjectDetail VALUES(5,3,'ABC');

INSERT INTO ProjectDetail VALUES(6,4,'GHI');

INSERT INTO ProjectDetail VALUES(7,3,'XYZ');

INSERT INTO ProjectDetail VALUES(8,5,'DDC');

Alias: Aliases are often used to make column names more readable

Basic SQL Queries for Practice:

1. Write query to get all employee detail from "EmployeeDetail" table:

ANS: SELECT * FROM EmployeeDetail

2. Write query to get only "FirstName" column from " EmployeeDetail " table

ANS: SELECT FirstName FROM EmployeeDetail

3. Write query to get FirstName in upper case as "First Name".
ANS: SELECT UPPER(FirstName) AS [First Name] FROM EmployeeDetail

4. Write query to get FirstName in lower case as "First Name".
ANS: SELECT LOWER(FirstName) AS [First Name] FROM EmployeeDetail

5. Write query for combine FirstName and LastName and display it as "Name" (also include white space between first name & last name).
1st Way - SELECT CONCAT(FirstName+' ',LastName) AS Name FROM EmployeeDetail
2nd Way - SELECT FirstName+' '+LastName AS Name FROM EmployeeDetail

Below are some very good websites to Practice SQL Queries online:

No comments:

Post a Comment