SQL Training: Exercise on Clauses,String and Mathematical functions,Date Time and Aggregate Functions
Exercise 3:
1. WAQ to display Tool Designer designation employee’s details.
2. WAQ to display Design Engineer and Tool designation employee’s details.
3. WAQ to display female market specialist employee’s details.
4. WAQ to display single female employee’s details.
5. WAQ to display employees whose vocation hours between 40 and 50.
6. WAQ to display total number of hours away from work for each employee by adding the number of hours taken for vacation and the number of hours taken as sick leave.
7. WAQ to display employees whose job title contain engineer word.
8. WAQ to display employees whose vacation hours contain more than or equal to 40.
9. WAQ to display all employees except tool designer designation employees.
10.WAQ to display employee details whose vacation hours not in range 20 and 40.
11.WAQ to display employee details except tool designer and designer engineer.
12. WAQ to display employee details who joined from 2001-01-01 to 200-12-31.
13. WAQ to display design engineer married female details.
Exercise 4
1. WAQ to retrieve equivalent ASCII value of ‘a’
2. WAQ to retrieve equivalent ASCII value of ‘A’
3. WAQ to retrieve equivalent ASCII value of ‘India’
4. WAQ to retrieve equivalent character value for ASCII number 112
5. WAQ to retrieve equivalent character value for ASCII number 95
6. WAQ to display given string i.e. ‘INDIA’ in lowercase.
7. WAQ to display all Employee Names of EMP table in lowercase.
9. WAQ to display no of characters in the given string i.e. ‘India’
10. WAQ to display Employee Names, number of characters in their names of EMP table.
11. WAQ to display 1st letters of employee names from EMP table.
12. WAQ to display Absolute value of given number i.e. -1234
13. WAQ to display square of given number i.e. 5
14. WAQ to display square of sal of employees from EMP table
15. WAQ to display Square root of Salaries from EMP table
16.WAQ to display greatest integer that is less than or equal to given value i.e. 456.56.
17.WAQ to display least integer that is greater than or equal to given value i.e. 78.3
18. WAQ to display the value which rounds the given value to its previous integer. the given value is.
56.97
19. WAQ to display the rounded figure of the given number i.e. 96.7342,2
20. WAQ to display round value of average of salary of employee’s up to 3 digits from EMP table
21. WAQ to find starting position of specified expression in given character string.
For Example Specified Expression is ‘The’ and Character string is ‘the quick brown fox jumps over
The lazy dog’
22. WAQ to display first name and last name from full name.
For example full name is Ricky Pointing
23. WAQ to extract only website name from URL. For example URL is http://www.gmail.com
Exercise 5:
1. WAQ to display the current system date.
2. WAQ to retrieve Month of Hire date from EMP table.
3. WAQ to retrieve Year of Hire date from EMP table.
4. WAQ display Seconds of current system time.
5. WAQ to retrieve day of week of Hire date from EMP table.
6. WAQ to Display total no of Males and Female employees in the Organizations.
7. WAQ to Display total no of Married Males and Un Married Females in the Organizations.
8. WAQ to Display total no of Employee in designation wise.
9. WAQ to Display total no of Employees joined in the Organization Year Wise.
10. WAQ to Display Employee Details who joined on Sunday.
11. WAQ to Display Employee Details who joined in Date of Week 4.
12. WAQ to Display Employee Details who’s Vacation Hours between 40 and 50.
13. WAQ to Display Employee Details who joined in Week Ends.
14. WAQ to Display Current Age of all Employees based on Birthdates.
15. WAQ to Display total no of Males and Females joined in the year of 1981 as well as 1971.
MY SOLUTIONS:
-------------------------------
--EXERCISES ON CLAUSES
SELECT
MAX(SAL) AS HIGHESTSALPERDEPT
,DEPTNO
FROM dbo.emp
GROUP BY DEPTNO
SELECT
MAX(SAL) AS HIGHESTSALPERJOB
,JOB
FROM dbo.emp
GROUP BY JOB
SELECT
MAX(SAL) AS HIGHESTSALJOBPERDEPT
,DEPTNO
,JOB
FROM dbo.emp
GROUP BY DEPTNO,JOB
SELECT
COUNT(EMPNO) AS NOEMPPERDEPT
,DEPTNO
FROM dbo.emp
GROUP BY DEPTNO
SELECT
COUNT(EMPNO) AS MORETHANTHREEEMPPERDEPT
,DEPTNO
FROM dbo.emp
GROUP BY DEPTNO
HAVING COUNT(EMPNO)>3
SELECT
COUNT(JOB) AS NOCLERKPERDEPT
,DEPTNO
FROM dbo.emp
WHERE JOB = 'CLERK'
GROUP BY DEPTNO
SELECT
COUNT(JOB) AS MORETHANONECLERKPERDEPT
,DEPTNO
FROM dbo.emp
WHERE JOB = 'CLERK'
GROUP BY DEPTNO
HAVING COUNT(JOB)>1
SELECT
SUM(SAL) AS SUMOFSALINDEPTTWENTY
,DEPTNO
FROM dbo.emp
WHERE DEPTNO = 20
GROUP BY DEPTNO
SELECT
AVG(SAL) AS AVGSALPERDEPTGREATERTHAN2500
,DEPTNO
FROM dbo.emp
GROUP BY DEPTNO
HAVING AVG(SAL) > 2500
SELECT
SUM(SAL) AS SUMOFSALINDEPTWHENGREATERTHAN7000
,DEPTNO
FROM dbo.emp
GROUP BY DEPTNO
HAVING SUM(SAL) > 7000
SELECT EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
FROM dbo.emp
ORDER BY SAL ASC
SELECT EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
FROM dbo.emp
ORDER BY SAL DESC
-------------------------------------------------
-------------------------------------------------
--EXERCISE 4
SELECT ASCII('a') AS ASCIIVALUEOFa
SELECT ASCII('A') AS ASCIIVALUEOFA
SELECT ASCII('INDIA') AS ASCIIVALUEOFINDIA
SELECT CHAR(112) AS CHARVALUEOF112
SELECT CHAR(95) AS CHARVALUEOF95
SELECT LOWER('INDIA') AS LOWERCASEOFINDIA
SELECT LOWER(ENAME) AS LOWERCASEEMPNAMES
FROM dbo.emp
SELECT LEN('INDIA') AS LENOFSTRINGINDIA
SELECT
ENAME
,LEN(ENAME) AS LENOFEMPNAMES
FROM dbo.emp
SELECT
ENAME
,LEFT(ENAME,1) AS LENOFEMPNAMES
FROM dbo.emp
SELECT ABS(-1234) AS ABSOLUTEOFGIVENVALUE
SELECT SQUARE(5) AS SQUAREOFGIVENVALUE
SELECT SQUARE(SAL) AS SQUAREOFSALOFEMP
FROM dbo.emp
SELECT SQRT(SAL) AS SQUAREROOTSOFSALOFEMP
FROM dbo.emp
SELECT FLOOR(456.56) AS FLOOROFVALUE
SELECT CEILING(78.3) AS CEILINGOFVALUE
SELECT FLOOR(56.97) AS FLOOROFVALUE--FLOOR ROUNDS DOWN
SELECT ROUND(96.7342,2) AS ROUNDINGOFVALUE
SELECT
ROUND(AVG(SAL),3) AS AVGSALROUNDEDTO3DIGITS
FROM dbo.emp
SELECT CHARINDEX('The','the quick brown fox jumps over The lazy dog') AS POSITIONOFSPECIFIEDEXPRESSION
SELECT SUBSTRING('Ricky Pointing',charindex(' ','Ricky Pointing')- LEN('Ricky Pointing'),LEN('Ricky Pointing')) AS FIRSTNAME,
SUBSTRING('Ricky Pointing',charindex(' ','Ricky Pointing'),LEN('Ricky Pointing')) AS LASTNAME
SELECT SUBSTRING('http://www.gmail.com',charindex('/','http://www.gmail.com')+2,LEN('http://www.gmail.com')) AS URL
-------------------------------------------------
--------------------------------------------------
--DATE TIME AND AGGREGATE FUNCTIONS
SELECT GETDATE()
SELECT MONTH(HIREDATE) AS MONTHOFHIREDATE
FROM dbo.emp
SELECT YEAR(HIREDATE) AS YEAROFHIREDATE
FROM dbo.emp
SELECT DATEPART(SS,GETDATE()) AS SECONDSOFGETDATE
SELECT DATENAME(WEEKDAY,HIREDATE) AS DAYOFWEEKOFHIREDATE
FROM dbo.emp
SELECT COUNT(BusinessEntityID) AS COUNTOFMALEANDFEMEMP
FROM HumanResources.Employee
GROUP BY Gender
SELECT COUNT(BusinessEntityID) AS COUNTOFMARRMALEANDUNMARRFEMEMP
FROM HumanResources.Employee
WHERE Gender = 'M' AND MaritalStatus = 'M'
UNION
SELECT COUNT(BusinessEntityID) AS COUNTOFMARRMALANDUNMARFEMEMP
FROM HumanResources.Employee
WHERE Gender = 'F' AND MaritalStatus = 'S'
SELECT COUNT(BusinessEntityID) AS COUNTOFEMPLOYEESPERDES
FROM HumanResources.Employee
GROUP BY JobTitle
SELECT COUNT(BusinessEntityID) AS COUNTOFEMPLOYEESPERHIREYEAR
FROM HumanResources.Employee
GROUP BY YEAR(HireDate)
SELECT e.BusinessEntityID,
e.JobTitle,
e.BirthDate,
e.MaritalStatus,
e.Gender,
e.HireDate,
e.VacationHours,
e.SickLeaveHours
FROM HumanResources.Employee AS e
WHERE DATENAME(WEEKDAY,e.HireDate) = 'Sunday'
SELECT e.BusinessEntityID,
e.JobTitle,
e.BirthDate,
e.MaritalStatus,
e.Gender,
e.HireDate,
e.VacationHours,
e.SickLeaveHours
FROM HumanResources.Employee AS e
WHERE DATEPART(WEEKDAY,e.HireDate) = 4
SELECT e.BusinessEntityID,
e.JobTitle,
e.BirthDate,
e.MaritalStatus,
e.Gender,
e.HireDate,
e.VacationHours,
e.SickLeaveHours
FROM HumanResources.Employee AS e
WHERE e.VacationHours BETWEEN 40 AND 50
ORDER BY e.VacationHours
SELECT e.BusinessEntityID,
e.JobTitle,
e.BirthDate,
e.MaritalStatus,
e.Gender,
e.HireDate,
e.VacationHours,
e.SickLeaveHours
FROM HumanResources.Employee AS e
WHERE DATEPART(WEEKDAY,e.HireDate) IN (6,7)
SELECT (YEAR(GETDATE()) - YEAR(BirthDate)) AS CURRENTAGE
FROM HumanResources.Employee
SELECT COUNT(BusinessEntityID) AS COUOFMALEANDFEMEMPJOIINGIVENYEARS
FROM HumanResources.Employee
WHERE Gender = 'M' AND YEAR(HireDate) IN (1971,1981)
UNION
SELECT COUNT(BusinessEntityID) AS COUOFMALEANDFEMEMPJOIINGIVENYEARS
FROM HumanResources.Employee
WHERE Gender = 'F' AND YEAR(HireDate) IN (1971,1981)
1. WAQ to display Tool Designer designation employee’s details.
2. WAQ to display Design Engineer and Tool designation employee’s details.
3. WAQ to display female market specialist employee’s details.
4. WAQ to display single female employee’s details.
5. WAQ to display employees whose vocation hours between 40 and 50.
6. WAQ to display total number of hours away from work for each employee by adding the number of hours taken for vacation and the number of hours taken as sick leave.
7. WAQ to display employees whose job title contain engineer word.
8. WAQ to display employees whose vacation hours contain more than or equal to 40.
9. WAQ to display all employees except tool designer designation employees.
10.WAQ to display employee details whose vacation hours not in range 20 and 40.
11.WAQ to display employee details except tool designer and designer engineer.
12. WAQ to display employee details who joined from 2001-01-01 to 200-12-31.
13. WAQ to display design engineer married female details.
Exercise 4
1. WAQ to retrieve equivalent ASCII value of ‘a’
2. WAQ to retrieve equivalent ASCII value of ‘A’
3. WAQ to retrieve equivalent ASCII value of ‘India’
4. WAQ to retrieve equivalent character value for ASCII number 112
5. WAQ to retrieve equivalent character value for ASCII number 95
6. WAQ to display given string i.e. ‘INDIA’ in lowercase.
7. WAQ to display all Employee Names of EMP table in lowercase.
9. WAQ to display no of characters in the given string i.e. ‘India’
10. WAQ to display Employee Names, number of characters in their names of EMP table.
11. WAQ to display 1st letters of employee names from EMP table.
12. WAQ to display Absolute value of given number i.e. -1234
13. WAQ to display square of given number i.e. 5
14. WAQ to display square of sal of employees from EMP table
15. WAQ to display Square root of Salaries from EMP table
16.WAQ to display greatest integer that is less than or equal to given value i.e. 456.56.
17.WAQ to display least integer that is greater than or equal to given value i.e. 78.3
18. WAQ to display the value which rounds the given value to its previous integer. the given value is.
56.97
19. WAQ to display the rounded figure of the given number i.e. 96.7342,2
20. WAQ to display round value of average of salary of employee’s up to 3 digits from EMP table
21. WAQ to find starting position of specified expression in given character string.
For Example Specified Expression is ‘The’ and Character string is ‘the quick brown fox jumps over
The lazy dog’
22. WAQ to display first name and last name from full name.
For example full name is Ricky Pointing
23. WAQ to extract only website name from URL. For example URL is http://www.gmail.com
Exercise 5:
1. WAQ to display the current system date.
2. WAQ to retrieve Month of Hire date from EMP table.
3. WAQ to retrieve Year of Hire date from EMP table.
4. WAQ display Seconds of current system time.
5. WAQ to retrieve day of week of Hire date from EMP table.
6. WAQ to Display total no of Males and Female employees in the Organizations.
7. WAQ to Display total no of Married Males and Un Married Females in the Organizations.
8. WAQ to Display total no of Employee in designation wise.
9. WAQ to Display total no of Employees joined in the Organization Year Wise.
10. WAQ to Display Employee Details who joined on Sunday.
11. WAQ to Display Employee Details who joined in Date of Week 4.
12. WAQ to Display Employee Details who’s Vacation Hours between 40 and 50.
13. WAQ to Display Employee Details who joined in Week Ends.
14. WAQ to Display Current Age of all Employees based on Birthdates.
15. WAQ to Display total no of Males and Females joined in the year of 1981 as well as 1971.
MY SOLUTIONS:
-------------------------------
--EXERCISES ON CLAUSES
SELECT
MAX(SAL) AS HIGHESTSALPERDEPT
,DEPTNO
FROM dbo.emp
GROUP BY DEPTNO
SELECT
MAX(SAL) AS HIGHESTSALPERJOB
,JOB
FROM dbo.emp
GROUP BY JOB
SELECT
MAX(SAL) AS HIGHESTSALJOBPERDEPT
,DEPTNO
,JOB
FROM dbo.emp
GROUP BY DEPTNO,JOB
SELECT
COUNT(EMPNO) AS NOEMPPERDEPT
,DEPTNO
FROM dbo.emp
GROUP BY DEPTNO
SELECT
COUNT(EMPNO) AS MORETHANTHREEEMPPERDEPT
,DEPTNO
FROM dbo.emp
GROUP BY DEPTNO
HAVING COUNT(EMPNO)>3
SELECT
COUNT(JOB) AS NOCLERKPERDEPT
,DEPTNO
FROM dbo.emp
WHERE JOB = 'CLERK'
GROUP BY DEPTNO
SELECT
COUNT(JOB) AS MORETHANONECLERKPERDEPT
,DEPTNO
FROM dbo.emp
WHERE JOB = 'CLERK'
GROUP BY DEPTNO
HAVING COUNT(JOB)>1
SELECT
SUM(SAL) AS SUMOFSALINDEPTTWENTY
,DEPTNO
FROM dbo.emp
WHERE DEPTNO = 20
GROUP BY DEPTNO
SELECT
AVG(SAL) AS AVGSALPERDEPTGREATERTHAN2500
,DEPTNO
FROM dbo.emp
GROUP BY DEPTNO
HAVING AVG(SAL) > 2500
SELECT
SUM(SAL) AS SUMOFSALINDEPTWHENGREATERTHAN7000
,DEPTNO
FROM dbo.emp
GROUP BY DEPTNO
HAVING SUM(SAL) > 7000
SELECT EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
FROM dbo.emp
ORDER BY SAL ASC
SELECT EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
FROM dbo.emp
ORDER BY SAL DESC
-------------------------------------------------
-------------------------------------------------
--EXERCISE 4
SELECT ASCII('a') AS ASCIIVALUEOFa
SELECT ASCII('A') AS ASCIIVALUEOFA
SELECT ASCII('INDIA') AS ASCIIVALUEOFINDIA
SELECT CHAR(112) AS CHARVALUEOF112
SELECT CHAR(95) AS CHARVALUEOF95
SELECT LOWER('INDIA') AS LOWERCASEOFINDIA
SELECT LOWER(ENAME) AS LOWERCASEEMPNAMES
FROM dbo.emp
SELECT LEN('INDIA') AS LENOFSTRINGINDIA
SELECT
ENAME
,LEN(ENAME) AS LENOFEMPNAMES
FROM dbo.emp
SELECT
ENAME
,LEFT(ENAME,1) AS LENOFEMPNAMES
FROM dbo.emp
SELECT ABS(-1234) AS ABSOLUTEOFGIVENVALUE
SELECT SQUARE(5) AS SQUAREOFGIVENVALUE
SELECT SQUARE(SAL) AS SQUAREOFSALOFEMP
FROM dbo.emp
SELECT SQRT(SAL) AS SQUAREROOTSOFSALOFEMP
FROM dbo.emp
SELECT FLOOR(456.56) AS FLOOROFVALUE
SELECT CEILING(78.3) AS CEILINGOFVALUE
SELECT FLOOR(56.97) AS FLOOROFVALUE--FLOOR ROUNDS DOWN
SELECT ROUND(96.7342,2) AS ROUNDINGOFVALUE
SELECT
ROUND(AVG(SAL),3) AS AVGSALROUNDEDTO3DIGITS
FROM dbo.emp
SELECT CHARINDEX('The','the quick brown fox jumps over The lazy dog') AS POSITIONOFSPECIFIEDEXPRESSION
SELECT SUBSTRING('Ricky Pointing',charindex(' ','Ricky Pointing')- LEN('Ricky Pointing'),LEN('Ricky Pointing')) AS FIRSTNAME,
SUBSTRING('Ricky Pointing',charindex(' ','Ricky Pointing'),LEN('Ricky Pointing')) AS LASTNAME
SELECT SUBSTRING('http://www.gmail.com',charindex('/','http://www.gmail.com')+2,LEN('http://www.gmail.com')) AS URL
-------------------------------------------------
--------------------------------------------------
--DATE TIME AND AGGREGATE FUNCTIONS
SELECT GETDATE()
SELECT MONTH(HIREDATE) AS MONTHOFHIREDATE
FROM dbo.emp
SELECT YEAR(HIREDATE) AS YEAROFHIREDATE
FROM dbo.emp
SELECT DATEPART(SS,GETDATE()) AS SECONDSOFGETDATE
SELECT DATENAME(WEEKDAY,HIREDATE) AS DAYOFWEEKOFHIREDATE
FROM dbo.emp
SELECT COUNT(BusinessEntityID) AS COUNTOFMALEANDFEMEMP
FROM HumanResources.Employee
GROUP BY Gender
SELECT COUNT(BusinessEntityID) AS COUNTOFMARRMALEANDUNMARRFEMEMP
FROM HumanResources.Employee
WHERE Gender = 'M' AND MaritalStatus = 'M'
UNION
SELECT COUNT(BusinessEntityID) AS COUNTOFMARRMALANDUNMARFEMEMP
FROM HumanResources.Employee
WHERE Gender = 'F' AND MaritalStatus = 'S'
SELECT COUNT(BusinessEntityID) AS COUNTOFEMPLOYEESPERDES
FROM HumanResources.Employee
GROUP BY JobTitle
SELECT COUNT(BusinessEntityID) AS COUNTOFEMPLOYEESPERHIREYEAR
FROM HumanResources.Employee
GROUP BY YEAR(HireDate)
SELECT e.BusinessEntityID,
e.JobTitle,
e.BirthDate,
e.MaritalStatus,
e.Gender,
e.HireDate,
e.VacationHours,
e.SickLeaveHours
FROM HumanResources.Employee AS e
WHERE DATENAME(WEEKDAY,e.HireDate) = 'Sunday'
SELECT e.BusinessEntityID,
e.JobTitle,
e.BirthDate,
e.MaritalStatus,
e.Gender,
e.HireDate,
e.VacationHours,
e.SickLeaveHours
FROM HumanResources.Employee AS e
WHERE DATEPART(WEEKDAY,e.HireDate) = 4
SELECT e.BusinessEntityID,
e.JobTitle,
e.BirthDate,
e.MaritalStatus,
e.Gender,
e.HireDate,
e.VacationHours,
e.SickLeaveHours
FROM HumanResources.Employee AS e
WHERE e.VacationHours BETWEEN 40 AND 50
ORDER BY e.VacationHours
SELECT e.BusinessEntityID,
e.JobTitle,
e.BirthDate,
e.MaritalStatus,
e.Gender,
e.HireDate,
e.VacationHours,
e.SickLeaveHours
FROM HumanResources.Employee AS e
WHERE DATEPART(WEEKDAY,e.HireDate) IN (6,7)
SELECT (YEAR(GETDATE()) - YEAR(BirthDate)) AS CURRENTAGE
FROM HumanResources.Employee
SELECT COUNT(BusinessEntityID) AS COUOFMALEANDFEMEMPJOIINGIVENYEARS
FROM HumanResources.Employee
WHERE Gender = 'M' AND YEAR(HireDate) IN (1971,1981)
UNION
SELECT COUNT(BusinessEntityID) AS COUOFMALEANDFEMEMPJOIINGIVENYEARS
FROM HumanResources.Employee
WHERE Gender = 'F' AND YEAR(HireDate) IN (1971,1981)
Comments
Post a Comment