Please visit, subscribe and share 10 Minutes Lectures in Computer Science
Showing posts with label SQL Exercise. Show all posts
Showing posts with label SQL Exercise. Show all posts

# Structured Query Language exercise, SQL lab exercise solved, SQL join queries explained, Easy join, group by clauses and subqueries

## SQL solved exercise

Consider a social network database, about users and their relationships. The database has two relations:

Users (uid, name)

Relationship (uid1, rel, uid2)

Here uid is the key for Users relation; uid1 and uid2 are foreign keys and both are referencing uid of Users; rel is a string representing the relation type, and the value can be friend or enemy. Note that the relationship is not necessarily symmetric: if Alice is friend with Bob, this does not imply that Bob is friend with Alice.

Query 1:

Find the names of all friends of Alice.

Solution 1:

SELECT name FROM Users WHERE uid IN (SELECT uid2

FROM Users x, Relationship y

WHERE x.uid = y.uid1 AND x.name = ‘Alice’ AND y.rel = ‘friend’);

Explanation:

We need the names of Alice’s friends. The query has two parts;

Inner query: selects all the user ids of friends of Alice by joining the tables Users with Relationship. Mentioning the list of tables separated by comma in FROM clause represents basic Cartesian product join.

Outer query: to identify and display the names of the uids from the result of inner query.

IN in outer query: the result of inner query may consist of zero or more records. To include the names of all the uids that are in the result of inner query, the IN operator is used.

Query 2:

Write a SQL query that computes, for each user, the total number of their friends. Your query should return results containing the uid, the name, and the count. Note that your query must return exactly one answer for every user in Users.

Solution 2:

SELECT x.uid, x.name, count(*)

FROM Users x LEFT OUTER JOIN Relationship y

ON x.uid = y.uid1 AND y.rel='friend'

GROUP BY x.uid, x.name;

Explanation:

SELECT clause: We need the uid, name and the count of their friends. FROM clause: uid and name is in Users table. To find the number of friends, we need to join Users with Relationship.

JOIN...ON: To make a valid join, every record of both tables should be compared using common attributes (x.uid = y.uid1).

Condition: y.rel=’friend’ is included because we are interested only in the value ‘friend’.

GROUP BY clause: As per the question, we need to display uid and name along with the count of friends. To include attributes along with an aggregate function (count) in SELECT clause, we need to group the results on the attributes needed.

Note: the names x and y in the above queries are called as tuple variables or aliases or rename variables. They are useful in disambiguating the attribute names in case if attribute names of two different tables are same. In these queries, they are not needed.

*************

# SQL Cheat sheet for beginners, SELECT clause, FROM clause, WHERE clause explained with examples.

## SELECT ... FROM ... WHERE clauses in SQL - Cheat sheet

 Querying data using SELECT…FROM...WHERE clauses in SQL Commands/Clauses Examples SELECT ðŸ‘ˆ To view required data from one or more tables.   Syntax: SELECT parameters FROM tablename;   Parameters for SELECT: *, list of attributes, functions, arithmetic operations are the parameters for the command (clause) SELECT. Parameters examples for SELECT clause Simple: SELECT * FROM student; Result: Displays all columns of all records of student table. List of attributes: SELECT name, phone FROM student; Result: Displays only name and phone of all students; Function: SELECT MAX(salary) FROM employee; Result: Displays the maximum of all salary values stored in employee table. Operations: SELECT Basic + HRA FROM employee; Result: Displays the sum of the values stored in Basic and HRA columns (attributes) of all employee records. FROM ðŸ‘ˆ To specify the name of the table/view from which we want to view the data.   Syntax: SELECT parameters FROM tablename; Parameters for FROM: Table name or table names separated with comma. Parameters examples for FROM clause Single table: SELECT * FROM employee; Result: Displays all records from employee table. Multiple tables: SELECT * FROM employee, dept; Result: It is a special case. It performs join. Displays columns of tables, employee and dept with every record of table employee combined with every record of dept. WHERE ðŸ‘ˆ To specify one or more conditions to filter the data to view/get required data only.   Syntax: SELECT parameters FROM tablename WHERE condition;   Parameters for WHERE: WHERE clause accepts the conditions (predicates) of the following format; Attribute_name OP value; Attribute_name OP attribute_name; Here, OP refers to OPERATOR that can be one of =, <>, >, <, >=, and <= More conditions can be mentioned in WHERE clause. In that case, the conditions should be connected using logical connectives AND, OR, NOT. Parameter examples for WHERE clause attribute_name OP value: SELECT * FROM employee WHERE gender = ‘male’; Result: Displays all records of employee who are MALE.  SELECT * FROM employee WHERE salary > 10000; Result: Displays all records of employees who earn more than 10000.  SELECT name, age FROM employee WHERE salary = 25000; Result: Displays only name and ages of all employees who earn 25000. attribute_name OP attribute_name: SELECT * FROM employee, dept WHERE emp_dno = dept_dno;

***********

# Multiple choices questions in DBMS, Structured Query Language, Find equivalent alternate queries for the given SQL queries, Can we write equivalent alternate queries to an SQL query?

## DBMS MCQ - Suggesting alternate equivalent queries to an SQL query

1. Consider a table STU with schema STU(Regno, Name, DOB, Phone) and Regno as the primary key. Which of the following queries is equivalent to the query "SELECT * FROM stu WHERE Regno=1 AND Phone=2;" Choose all queries that are equivalent.

a) select * from stu where regno=1 and phone=(select phone from stu where phone=2 order by phone fetch first 1 rows only);

b) select * from stu where regno=1 and phone in (select phone from stu where phone=2);

c) select * from stu where regno=1 and phone in (1, 2);

d) select * from stu where phone=2 and regno in (1, 2, 3);

 Answer: (a) select * from stu where regno=1 and phone=(select phone from stu where phone=1 order by phone fetch first 1 rows only); and (b) select * from stu where regno=1 and phone in (select phone from stu where phone=2); The query given in the question finds and displays all records that have 1 in register number attribute (column) and 2 in phone attribute from STU table. Option (a) does the same thing using a sub-query. The sub-query here fetches all the phone numbers that are equivalent to 2. As phone attribute is not a key attribute, the result may consist of more than 1 entry. Hence, we use the ORDER BY clause and FETCH to include only the top 1 result. Option (b) is does the same thing as the given query hence it is EQUIVALENT. Option (c) is NOT EQUIVALENT because it accepts the record (1, 1) also which is not expected as per the given query. Option (d) is NOT EQUIVALENT because it accepts records (1, 2), (2, 2) and (3, 2).

************************
Related posts:

## Featured Content

### Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

data recovery