The modification of information stored in the database Query Language A query is a statement requesting the retrieval of information. The portion of a DML that involves information retrieval is called a query language. Although technically incorrect, it is common practice to use the terms query language and data-manipulation language synonymously. The SELECT Operation / Statement The SELECT statement is used to retrieve information from the database according to provided conditions. To demonstrate the use of SELECT statement, let’s assume the following two relation instances. customer_name customer_street customer_city account_number customer_name balance Somebody Mirpur Road Dhaka A-101 Anybody 1000 Somebody Aga Kha Road Bogra A-102 Anybody 1500 Anybody XYZ Road Khagrachhori A-103 Somebody 2000 Nobody Mirpur Road Dhaka A-104 Nobody 2500 customer account Q1. From the customer table, find out the information of all the customers who live in Dhaka. select * from customer where customer_city = 'Dhaka'; Output: customer_name customer_street customer_city Somebody Mirpur Road Dhaka Nobody Mirpur Road Dhaka Q2. From the customer table, find out the information of all the customers who live in either Dhaka or Bogra. select * from customer where customer_city = 'Dhaka' or customer_city = 'Bogra'; Output: customer_name customer_street customer_city Somebody Mirpur Road Somebody Aga Kha Road Bogra Nobody Mirpur Road Q3. Find out the customers along with their account numbers who have account balances of at least 2000 taka. select customer_name, account_number from account where balance >= 2000; Output: customer_name account_number Somebody A-103 Nobody A-104 From the above three queries, we can observe the general structure for the select statement: SELECT attribute_1, attribute_2, ... , attribute_N FROM WHERE expression The attributes after the SELECT phrase are those attributes which we want to display at the output result.
If an asterisk (*) is placed the select phrase instead of attribute names, it would mean that we want all the attributes at the output. The table name after the FROM clause refers to the relation from which we want to perform the query. The expressions after the WHERE clause contains the actual query. The expressions may contain the following operators: Comparison operators: <, <=, >, >=, =, <> Logical connectives: AND, OR, NOT Simplified operators: this BETWEEN some_value AND some_other_value, this NOT BETWEEN some_value AND some_other_value [These are the substitutes for: this <= some_value AND this >= some_other_value] Q4. From the following table named result, find out the students (along with their marks) who have scored more than 80 marks, and display them in the descending order of their marks. student_name A 55 B 90 C 40 D 80 E 85 F 95 G 82 select student_name, marks from result where marks > 80 order by marks desc; Or, as the table contains only these two fields, we can write: select * from result where marks > 80 order by marks desc; Output: student_name F 95 B 90 E 85 G 82 Q5. From the above table in Q4, we want to find out the top-most 3 students (to give them prizes for obtaining the 1st, 2nd and 3rd places). How would you find them? select student_name from result order by marks desc limit 3; Output: student_name F B E Q6. From the table in Q4, we want to find out the student who stood 2nd. How would you find him? select student_name from result order by marks desc limit 1, 1; Output: student_name B
OR, select customer_name from depositor full outer join borrower using (customer_name) where loan_number is null or account_number is null OR, select customer_name from depositor natural full join borrower where loan_number is null or account_number is null RA: П customer_name (σ loan_number = null account_number = null (depositor borrower)) Output: customer_name Nobody Generalized Projection This operation extends the projection operation by allowing arithmetic functions to be used in the projection list. As an example, let’s revisit query Q8: we want to know how the results of the students (in Q4) would look like if we were to give away 5 marks as grace. How would we find it? SQL: select student_name, + 5 from result; RA: П student_name, marks + 5 (result) Aggregate Functions Aggregate functions take a collection of values and return a single value as a result. For example, consider the following employee table: employee_name branch_name branch_city salary A DU Dhaka 1000 B DU Dhaka 2000 C BUET Dhaka 3000 D KUET Khulna 4000 E KU Khulna 5000 F RU Rajshahi 6000 Q20. Find the number of branches appearing in the employee relation. SQL: select branch_name) from employee; RA: G count-distinct(branch_name) (employee) Output: count(distinct branch_name) 5 Q21. Find the total salary of all employees at each branch of the bank. SQL: select branch_name, sum(salary) from employee group by branch_name; RA: branch_nameG sum(salary) (employee) Output: branch_name sum(salary) BUET 3000