Scribd
Upload
75 views6 pages

DBMS Lab Report 06 - Ankit Pangeni

Uploaded by

sabin Bhandari
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
75 views6 pages

DBMS Lab Report 06 - Ankit Pangeni

Uploaded by

sabin Bhandari
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

B.Sc.

CSIT
4 Semesterth

Database Management System: CSC260

Lab-06 (Continue From Lab 05 dbms3)

OBJECTIVE

TO STUDY AND IMPLEMENT WHERE, SELECT, WILDCARDS, LIMIT AND AGGREGATE FUNCTIONS.

REQUIRED THEORY
 The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified
condition. It is used to extract only those records that fulfill a specified condition. The WHERE clause is
not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.! Its syntax with WHERE
clause is:

 A wildcard character is used to substitute one or more characters in a string. Wildcard characters are
used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified
pattern in a column. ex: LIKE 'a%' Finds any values that starts with "a"

 An aggregate function in SQL performs a calculation on multiple values and returns a single value. SQL
provides many aggregate functions that include avg, count, sum, min, max, etc. An aggregate function
ignores NULL values when it performs the calculation, except for the count function.

 The SQL SELECT LIMIT statement is used to retrieve records from one or more tables in a database and
limit the number of records returned based on a limit value.

QUESTIONS
Table: Same as Lab 05. Use database dbms3.

Using Where Clause and SQL ‘Like’ operator

1. Display a list of Publishers where publisher name starts with the letter „r‟

2. Display a list of Publishers where publisher name ends with the letter „n‟

3. Display a list of Books where book name contain the letter „en‟

Using Where Clause and multiple operators: Like, Order By

4. Display a list of Books where the author‟s name ends with „ce‟, order the result in descending order by
book name.

Using Where Clause and SQL ‘In’ operator

5. Display a list Publishers whose address is Puchong or Subang

6. Display a list of Books which price is 50 or 100

Submitted by: Ankit Pangeni, Roll : 04


Using SQL TOP() Function

7. Select top 2 books from table where price is less than 130

Using SQL MIN() Function

8. Display the lowest price of Book (in the result table, give the column name as “LowestPrice”)

Using SQL MAX() Function

9. Display the highest price of the Book (in the result table, give the column name as

“HighestPrice”)

Using SQL SUM() Function

10. Get the total value of the "Price" column from the "Book" table. (in the result table, give the

column name as “TotalPrice”)

Using SQL AVG() Function

11. Get the average value of the "Price" column from the "Book" table. (in the result table, give

the column name as “AveragePrice”)

Using SQL COUNT() Function

12. Find the total number of records in the Book table. (in the result table, give the column

name as “TotalBooks”)

13. Find how many books cost more than 60 rupees. (in the result table, give the column name

as “BooksCostMoreThan60”)

Using SQL COUNT() Function & GROUP BY

14. Find the total number of records in the Book table group by each author. (in the result table,

give the column name as “TotalBooksByAuthor”)

Using SQL SUM() Function & GROUP BY

15. Find the total Price of the books group by each author. (in the result table, give the column

name as “BookPriceByAuthor”)

Submitted by: Ankit Pangeni, Roll : 04


COMMANDS AND OUTPUTS

Question 1:

Question 2:

Submitted by: Ankit Pangeni, Roll : 04


Question 3:

Question 4:

Question 5:

Question 6:

Question 7:

Submitted by: Ankit Pangeni, Roll : 04


Question 8:

Question 9:

Question 10:

Question 11:

Question 12:

Submitted by: Ankit Pangeni, Roll : 04


Question 13:

Question 14:

Question 15:

Submitted by: Ankit Pangeni, Roll : 04

You might also like