CHANDIGARH UNIVERSITY Institute of Distance and Online Learning Course Development Committee Prof. (Dr.) R.S.Bawa Pro Chancellor, Chandigarh University, Gharuan, Punjab Advisors Prof. (Dr.) Bharat Bhushan, Director – IGNOU Prof. (Dr.) Majulika Srivastava, Director – CIQA, IGNOU Programme Coordinators & Editing Team Master of Business Administration (MBA) Bachelor of Business Administration (BBA) Coordinator – Dr. Rupali Arora Coordinator – Dr. Simran Jewandah Master of Computer Applications (MCA) Bachelor of Computer Applications (BCA) Coordinator – Dr. Raju Kumar Coordinator – Dr. Manisha Malhotra Master of Commerce (M.Com.) Bachelor of Commerce (B.Com.) Coordinator – Dr. Aman Jindal Coordinator – Dr. Minakshi Garg Master of Arts (Psychology) Bachelor of Science (Travel &Tourism Management) Coordinator – Dr. Samerjeet Kaur Coordinator – Dr. Shikha Sharma Master of Arts (English) Bachelor of Arts (General) Coordinator – Dr. Ashita Chadha Coordinator – Ms. Neeraj Gohlan Academic and Administrative Management Prof. (Dr.) R. M. Bhagat Prof. (Dr.) S.S. Sehgal Executive Director – Sciences Registrar Prof. (Dr.) Manaswini Acharya Prof. (Dr.) Gurpreet Singh Executive Director – Liberal Arts Director – IDOL © No part of this publication should be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording and/or otherwise without the prior written permission of the authors and the publisher. SLM SPECIALLY PREPARED FOR CU IDOL STUDENTS Printed and Published by: TeamLease Edtech Limited www.teamleaseedtech.com CONTACT NO:- 01133002345 For: CHANDIGARH UNIVERSITY Institute of Distance and Online Learning
DATABASE MANAGEMENT SYSTEM PRACTICAL (PRACTICAL) Course Code: BCA137 Credits: 1 COURSE OBJECTIVES • To give broad level understanding of database concept. • To expose students to design and development of databases. • Analyze basic structure of database and apply various types of queries. PRACTICAL 1:.INSTALLATION OF DATABASE MANAGEMENT SYSTEM. Solution: 1 Install SQL. a. Check compatible versions. b. Choose New SQL Server stand-alone installation. c. Include any product updates. d. Accept the defaults in Feature Selection, Instance Configuration, and Server Configuration. e. In Database Engine Configuration, under Authentication Mode, choose Mixed Mode. f. Accept the defaults in remaining dialogs.
2. Create a SQL database for your website. a. Start the Microsoft SQL Server Management Studio app. b. In the Object Explorer panel, right-click on Databases, and choose New Database.... c. Enter the name of the new database. Click/Tap OK
3. Create a SQL user account. a. Under Security, right-click on Logins, and choose New Login... b. Enter the username, choose SQL Server authentication, add a password, uncheck Enforce password policy, and choose the database.
4. Give the new SQL user db_owner access to the new database. a. In the Object Explorer panel, under your database > Security, right-click on Users, and choose New User. b. Set User type to SQL user with login. c. Enter the username of the account you just created, then click/tap Check Names d. Under Select a page, select Membership, and check the db_owner database role membership.
PRACTICAL 2: QUERY TO CREATE TABLE. Solution: 1 The CREATE TABLE statement is used to create a new table in a database. Syntax CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... ); SQL CREATE TABLE Example The following example creates a table called \"Persons\" that contains five columns: PersonID, LastName, FirstName, Address, and City: Example CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ); Output: PersonID LastName FirstName Address City
PRACTICAL 3: APPLY VARIOUS CONSTRAINTS ON THE TABLE. Solution: 1. SQL Create Constraints Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement. Syntax CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, .... ); NOT NULL: SQL NOT NULL on CREATE TABLE The following SQL ensures that the \"ID\", \"LastName\", and \"FirstName\" columns will NOT accept NULL values when the \"Persons\" table is created: Example CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );
UNIQUE: The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE (ID) ); PRIMARY KEY SQL PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields). SQL PRIMARY KEY on CREATE TABLE The following SQL creates a PRIMARY KEY on the \"ID\" column when the \"Persons\" table is created: MySQL: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL,
FirstName varchar(255), Age int, PRIMARY KEY (ID) ); FOREIGN KEY SQL FOREIGN KEY Constraint A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. Look at the following two tables: \"Persons\" table: PersonID LastName FirstName Age 1 Hansen Ola 30 2 Svendson Tove 23 3 Pettersen Kari 20 \"Orders\" table: OrderID OrderNumber PersonID 1 77895 3 2 44678 3 3 22456 2 4 24562 1
Notice that the \"PersonID\" column in the \"Orders\" table points to the \"PersonID\" column in the \"Persons\" table. The \"PersonID\" column in the \"Persons\" table is the PRIMARY KEY in the \"Persons\" table. The \"PersonID\" column in the \"Orders\" table is a FOREIGN KEY in the \"Orders\" table. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to. SQL FOREIGN KEY on CREATE TABLE The following SQL creates a FOREIGN KEY on the \"PersonID\" column when the \"Orders\" table is created: MySQL: CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );
PRACTICAL 4: QUERIES FOR INSERTING, DELETING, UPDATING AND SELECTING THE CONTENTS OF TABLE. Solution: 1. The CREATE TABLE Statement Is Used To Create A New Table In A Database. In That Table, If You Want To Add Multiple Columns, Use The Below Syntax. Syntax CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... ); The column parameters specify the names of the columns of the table. The data type parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.). Create Table Example CREATE TABLE Employee( EmpId int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255)
); The EmpId column is of type int and will hold an integer. The LastName, FirstName, Address, and City columns are of type varchar and will hold characters and the maximum length for these fields is 255 characters. Insert Value in this Table The INSERT INTO statement is used to insert new records in a table. It is possible to write the INSERT INTO statement in two ways. Syntax The first way specifies both the column names and the values to be inserted. If you are adding values for all the columns of the table, then no need to specify the column names in the SQL query. However, make sure that the order of the values is in the same order as the columns in the table. INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); '2nd way INSERT INTO table_name VALUES (value1, value2, value3, ...); Example Insert value in a 1st way. The column names are used here
INSERT INTO Employee (EmpId,LastName,FirstName,ADDRESS,City) VALUES (1, 'XYZ', 'ABC', 'India', 'Mumbai' ); INSERT INTO Employee (EmpId,LastName,FirstName,ADDRESS,City) VALUES (2, 'X', 'A', 'India', 'Pune' ); Insert value in a 2nd way. INSERT INTO Employee VALUES (3, 'XYZ', 'ABC', 'India', 'Mumbai' ); Select Statment in SQL The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set. SELECT column1, column2, ... FROM table_name; Here, column1, column2 ... are the field names of the table you want to select from the data. If you want to select all the fields available in the table, use the following syntax: SELECT * FROM table_name; If the above query is executed, then all record is displayed. Example Select EmpId, LastName from Employee; Select * from Employee; Update Table The UPDATE statement is used to modify the existing records in a table.
Syntax UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; Example UPDATE Employee SET FirstName= 'KS', City= 'Pune' WHERE EmpId= 1; If the above query is executed then for EmpId= 1, \"Firstname\" and \"City\" column data will be updated. Update Multiple Rows It is the WHERE clause that determines how many records will be updated. UPDATE Employee SET City='Pune' Delete Statment in SQL The DELETE statement is used to delete existing records in a table for a particular Record. Syntax DELETE FROM table_name WHERE condition; Example DELETE FROM Employee WHERE EmpId=1; In Employee table EmpId = 1 record gets deleted. Delete All Records
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact, DELETE FROM table_name; DELETE From Employee ; When the above query is executed, only table Data gets deleted.
PRACTICAL 5: PERFORM THE COMMANDS- TRUNCATE, DROP, ALTER ON THE TABLE. Solution: 1. TRUNCATE command TRUNCATE command removes all the records from a table. But this command will not destroy the table's structure. When we use TRUNCATE command on a table its (auto- increment) primary key is also initialized. Following is its syntax, TRUNCATE TABLE table_name Here is an example explaining it, TRUNCATE TABLE student; The above query will delete all the records from the table student. In DML commands, we will study about the DELETE command which is also more or less same as the TRUNCATE command. We will also learn about the difference between the two in that tutorial. 2. DROP command
DROP command completely removes a table from the database. This command will also destroy the table structure and the data stored in it. Following is its syntax, DROP TABLE table_name Here is an example explaining it, DROP TABLE student; The above query will delete the Student table completely. It can also be used on Databases, to delete the complete database. For example, to drop a database, DROP DATABASE Test; The above query will drop the database with name Test from the system. Alter command is applicable to add, modify, or update columns in an existing table. It is also applicable to drop or delete a table. The basic syntax of the alter command is as follows: Syntax: ALTER TABLE tableName operation columnName datatype; We can perform the following tasks using the Alter Command: 1. ADD Column in SQL Used to add a new column to the existing table. Syntax: ALTER TABLE tableName ADD columnName datatype ; Example 1: Let us add a gender column in the table.
Query: ALTER TABLE dataflair_employee ADD gender varchar(10); 2. MODIFY Column in SQL Used to modify the already existing columns in a database. We can modify one or multiple columns at once. Syntax: ALTER TABLE tableName MODIFY columnName columnType; The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.The ALTER TABLE statement is also used to add and drop various constraints on an existing table. ALTER TABLE - ADD Column To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype; The following SQL adds an \"Email\" column to the \"Customers\" table: Example ALTER TABLE Customers ADD Email varchar(255); Result: You have made changes to the database.
PRACTICAL 6: RESTRICT THE SELECTION OF DATA USING WHERE CLAUSE. Solution: 1. The essential capabilities of SELECT statement are Selection, Projection and Joining. Displaying specific columns from a table is known as a project operation. We will now focus on displaying specific rows of output. This is known as a select operation. Specific rows can be selected by adding a WHERE clause to a SELECT query. As a matter of fact, the WHERE clause appears just after the FROM clause in SELECT query hierarchy. The sequence has to be maintained in all scenarios. If violated, Oracle raises an exception. Syntax: SELECT *|{[DISTINCT] column| expression [alias],..} FROM table [WHERE condition(s)] In the syntax, WHERE clause is the keyword [Condition] contains column names, expressions, constants, literals and a comparison operator. Suppose that your manager is working on the quarterly budget for your organization. As part of this activity, it is necessary to produce a listing of each employee's essential details, but only for employees that are paid at least $25,000 annually. The SQL query below accomplishes this task. Note the use of the WHERE clause shown in bold text. SELECT Employee_ID, Last_Name, First_Name, Salary FROM employees
WHERE Salary >= 25000; EMPLOYEE_ID LAST_NAME FIRST_NAME SALARY 88303 88404 Jones Quincey $30,550.00 88505 Barlow William $27,500.00 Smith Susan $32,500.00 3 rows selected
PRACTICAL 7: QUERIES TO SHOW THE USAGE OF VARIOUS OPERATORS. Solution: SQL Arithmetic Operators 1. Assume 'variable a' holds 10 and 'variable b' holds 20, then − Show Examples Operator Description Example + (Addition) Adds values on either side of the operator. a + b will give 30 - (Subtraction) Subtracts right hand operand from left hand operand. a - b will give -10 * (Multiplication) Multiplies values on either side of the operator. a * b will give 200 / (Division) Divides left hand operand by right hand operand. b / a will give 2 % (Modulus) Divides left hand operand by right hand operand and returns remainder. b % a will give 0 SQL Comparison Operators Assume 'variable a' holds 10 and 'variable b' holds 20, then − Show Examples Operator Description Example = Checks if the values of two operands are equal or not, if yes then condition becomes true. (a = b) is not true. != Checks if the values of two operands are equal or not, if values are not equal
then condition becomes true. (a! = b) is true. <> Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a <> b) is true. > Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. (a > b) is not true. < Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. (a < b) is true. >= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. (a >= b) is not true. <= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. (a <= b) is true. !< Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true. (a! < b) is false. !> Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true. (a!> b) is true. SQL Logical Operators Here is a list of all the logical operators available in SQL. Show Examples Sr.No. Operator & Description 1 ALL The ALL operator is used to compare a value to all values in another value set. 2
AND The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause. 3 ANY The ANY operator is used to compare a value to any applicable value in the list as per the condition. 4 BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value. 5 EXISTS The EXISTS operator is used to search for the presence of a row in a specified table that meets a certain criterion. IN The IN operator is used to compare a value to a list of literal values that have been specified. 7 LIKE
The LIKE operator is used to compare a value to similar values using wildcard operators. 8 NOT The NOT operator reverses the meaning of the logical operator with which it is used. E.g.: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator. 9 OR The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause. 10 IS NULL The NULL operator is used to compare a value with a NULL value. 11 UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).
PRACTICAL 8: USE OF IN, BETWEEN, LIKE. Solution: 1. Between Operator: The BETWEEN operator in SQL Server is used to get the values within a range. Generally, we use the BETWEEN operator in the WHERE clause to get values within a specified range. For example, if you want to fetch all the employees between ID 3 and 7 from the Employee table, then you need to use the BETWEEN operator as shown below. SELECT * FROM Employee WHERE ID BETWEEN 3 AND 7 The above SQL statement will return records from the Employee table where the employee ID is between 3 and 7. Following is the output of the above SQL query. 2. In Operator: The IN Operator in SQL Server is used to search for specified values that match any value in the set of multiple values it accepts. Generally, we will use this IN operator in WHERE clause to compare column or variable values with a set of multiple values. For example, if you want to fetch all the employees whose department is either IT or HR, then you could write the SQL Query using the IN Operator as shown below. SELECT * FROM Employee WHERE Department IN (‘IT’, ‘HR’) Following is the output of the above SQL query.
3. Like Operator: This is one of the most frequently used operators in SQL Server. The LIKE operator in SQL Server is used to search for character string with the specified pattern using wildcards in the column. In SQL Server, pattern means its specific string of characters with wildcards to search for matched expressions. Generally, we will use this LIKE operator in the WHERE clause. Here, we will try to understand the LIKE operator using different types of wildcard characters. Using ‘%’ wildcard Example1: The following SQL query will return all employees whose name starts with the character ‘P’ followed by any string of characters. This is because here we mentioned a pattern like ‘P%’. Here ‘%’ is a wildcard character that we will use before or after characters to search for the required matched string of characters. SELECT * FROM Employee WHERE Name LIKE ‘P%’ Following is the output of the above SQL query.
PRACTICAL 9: USE OF ORDER BY, GROUP BY AND HAVING. Solution: 1. SQL GROUP BY Syntax Now that we know what the SQL GROUP BY clause is, let's look at the syntax for a basic group by query. The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions. i.e., if a particular column has same values in different rows, then it will arrange these rows in a group. GROUP BY clause is used with the SELECT statement. In the query, GROUP BY clause is placed after the WHERE clause. In the query, GROUP BY clause is placed before ORDER BY clause if used any. Syntax: SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 ORDER BY column1, column2; function_name: Name of the function used for example, SUM (), AVG (). table_name: Name of the table. condition: Condition used.
Employee Student Example: Group By single column: Group By single column means, to place all the rows with same value of only that particular column in one group. Consider the query as shown below: SELECT NAME, SUM(SALARY) FROM Employee GROUP BY NAME; The above query will produce the below output:
so, you can see in the above output, the rows with duplicate NAMEs are grouped under same NAME and their corresponding SALARY is the sum of the SALARY of duplicate rows. The SUM () function of SQL is used here to calculate the sum. Group By multiple columns: Group by multiple columns is say for example, GROUP BY column1, column2. This means to place all the rows with same values of both the columns column1 and column2 in one group. Consider the below query: SELECT SUBJECT, YEAR, Count(*) FROM Student GROUP BY SUBJECT, YEAR; Output: As you can see in the above output the students with both same SUBJECT and YEAR are placed in same group. And those whose only SUBJECT is same but not YEAR belongs to different groups. So here we have grouped the table according to two columns or more than one column. Grouping and aggregate functions Suppose we want total number of males and females in our database. We can use the following script shown below to do that. SELECT `gender`,COUNT(`membership_number`) FROM `members` GROUP BY `gender`; Executing the above script in MySQL workbench against the myflixdb gives us the following results.
gender COUNT('membership_number') Female3 Male 5 The results shown below are grouped by every unique gender value posted and the number of grouped rows is counted using the COUNT aggregate function. Restricting query results using the HAVING clause It's not always that we will want to perform groupings on all the data in a given table. There will be times when we will want to restrict our results to a certain given criteria. In such cases , we can use the HAVING clause Suppose we want to know all the release years for movie category id 8. We would use the following script to achieve our results. SELECT * FROM `movies` GROUP BY `category_id`,`year_released` HAVING `category_id` = 8; Executing the above script in MySQL workbench against the Myflixdb gives us the following results shown below. movie_id title director year_released category_id 9 Honey mooners Schultz 2005 8 5 Daddy's Little Girls NULL 2007 8 Note only movies with category id 8 have been affected by our GROUP BY clause.
PRACTICAL 10: USE OF AGGREGATE FUNCTIONS. Solution: 1. COUNT FUNCTION COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types. COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null. Syntax COUNT(*) or COUNT( [ALL|DISTINCT] expression ) Sample table: PRODUCT_MAST PRODUCT COMPANY QTY RATE COST 20 Item1 Com1 2 10 75 60 Item2 Com2 3 25 50 40 Item3 Com1 2 30 75 150 Item4 Com3 5 10 Item5 Com2 2 20 Item6 Com1 3 25 Item7 Com1 5 30
Item8 Com1 3 10 30 Item9 Com2 2 25 50 Item10 Com3 4 30 120 Example: COUNT() SELECT COUNT(*) FROM PRODUCT_MAST; Output: 10 Example: COUNT with WHERE SELECT COUNT(*) FROM PRODUCT_MAST; WHERE RATE>=20; Output: 7 Example: COUNT() with DISTINCT SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST; Output:
3 Example: COUNT() with GROUP BY SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY; Output: Com1 5 Com2 3 Com3 2 Example: COUNT() with HAVING SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY HAVING COUNT(*)>2; Output: Com1 5 Com2 3 2. SUM Function Sum function is used to calculate the sum of all selected columns. It works on numeric fields only. Syntax
SUM() or SUM( [ALL|DISTINCT] expression ) Example: SUM() SELECT SUM(COST) FROM PRODUCT_MAST; Output: 670 Example: SUM() with WHERE SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3; Output: 320 Example: SUM() with GROUP BY SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3 GROUP BY COMPANY; Output:
Com1 150 Com2 170 Example: SUM() with HAVING SELECT COMPANY, SUM(COST) FROM PRODUCT_MAST GROUP BY COMPANY HAVING SUM(COST)>=170; Output: Com1 335 Com3 170 3. AVG function The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values. Syntax AVG() or AVG( [ALL|DISTINCT] expression ) Example: SELECT AVG(COST) FROM PRODUCT_MAST;
Output: 67.00 4. MAX Function MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column. Syntax MAX() or MAX( [ALL|DISTINCT] expression ) Example: SELECT MAX(RATE) FROM PRODUCT_MAST; 30 5. MIN Function MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column. Syntax MIN() or MIN( [ALL|DISTINCT] expression ) Example:
SELECT MIN(RATE) FROM PRODUCT_MAST; Output: 10
PRACTICAL 11: COMBINING MULTIPLE TABLES USING JOIN. Solution: 1. The joins allow us to combine data from two or more tables so that we are able to join data of the tables so that we can easily retrieve data from multiple tables. You might ask yourself how many different types of join exist in SQL Server. The answer is there are four main types of joins that exist in SQL Server. First of all, we will briefly describe them using Venn diagram illustrations: • Inner join returns the rows that match in both tables • Left join returns all rows from the left table • Right join returns all rows from the right table • Full join returns whole rows from both tables
If you lack knowledge about the SQL join concept in the SQL Server, you can see the SQL Join types overview and tutorial article. After this short explanatory about the SQL joins types, we will go through the multiple joins. MULTIPLE JOINS Multiple joins can be described as follows; multiple join is a query that contains the same or different join types, which are used more than once. Thus, we gain the ability to combine multiple tables of data in order to overcome relational database issues. EXAMPLE: Green-Tree Company launched a new campaign for the New Year and made different offers to its online customers. As a result of their campaign, they succeeded in converting some offers to sales. In the following examples, we will uncover the New Year campaign data details of the Green-Tree company. The company stores these campaign data details in the following tables. Now, we will create these tables through the following query and populate them with some dummy data: -DROP TABLE IF EXISTS sales GO DROP TABLE IF EXISTS orders GO DROP TABLE IF EXISTS onlinecustomers GO CREATE TABLE onlinecustomers (customerid INT PRIMARY KEY IDENTITY(1,1) ,CustomerName VARCHAR(100) ,CustomerCity VARCHAR(100) ,Customermail VARCHAR(100)) GO CREATE TABLE orders (orderId INT PRIMARY KEY IDENTITY(1,1) , customerid INT ,
ordertotal float ,discountrate float ,orderdate DATETIME) GO CREATE TABLE sales (salesId INT PRIMARY KEY IDENTITY(1,1) , orderId INT , salestotal FLOAT) GO GO is even not a Transact-SQL statement, it is a command recognized by SQLCMD (analog of Oracle SQL*Plus) and SQL Server Management Studio Query tool that signals the end of the current SQL batch. When a GO command occurs, all statements entered since the last GO (or from the beginning of the script if this is the first GO) are sent to SQL Server as a single batch. A line with a GO command cannot contain any other statements, however it can contain comments. INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Salvador',N'Philadelphia',N'[email protected]') INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Gilbert',N'San Diego',N'[email protected]') INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Ernest',N'New York',N'[email protected]') INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Stella',N'Phoenix',N'[email protected]') INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Jorge',N'Los Angeles',N'[email protected]')
INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Jerome',N'San Antonio',N'[email protected]') INSERT INTO [dbo].[onlinecustomers]([CustomerName],[CustomerCity],[Customermail]) VALUES (N'Edward',N'Chicago',N'[email protected]') GO INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (3,1910.64,5.49,CAST('03-Dec-2019' AS DATETIME)) INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (4,150.89,15.33,CAST('11-Jun-2019' AS DATETIME)) INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (5,912.55,13.74,CAST('15-Sep-2019' AS DATETIME)) INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (7,418.24,14.53,CAST('28-May-2019' AS DATETIME)) INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (55,512.55,13.74,CAST('15-Jun-2019' AS DATETIME)) INSERT INTO [dbo].[orders]([customerid],[ordertotal],[discountrate],[orderdate]) VALUES (57,118.24,14.53,CAST('28-Dec-2019' AS DATETIME)) GO INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (3,370.95) INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (4,882.13) INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (12,370.95) INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (13,882.13) INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (55,170.95) INSERT INTO [dbo].[sales]([orderId],[salestotal]) VALUES (57,382.13)
In order to answer this question, we need to find out the matched rows for all the tables because some customers did not receive an email offer, and some offers could not be converted into a sale. The following Venn diagram will help us to figure out the matched rows which we need. In short, the result of this query should be the intersecting rows of all tables in the query. The grey-colored area specifies these rows in the Venn diagram: The SQL multiple joins approach will help us to join onlinecustomers, orders, and sales tables. As shown in the Venn diagram, we need to matched rows of all tables. For this reason, we will combine all tables with an inner join clause. The following query will return a result set that is desired from us and will answer the question: -SELECT customerName, customercity, customermail, salestotal FROM onlinecustomers AS oc INNER JOIN orders AS o ON oc.customerid = o.customerid INNER JOIN sales AS s ON o.orderId = s.orderId At first, we will analyze the query. An inner join clause that is between onlinecustomers and orders tables derived the matched rows between these
two tables. The second inner join clause that combines the sales table derived the matched rows from the previous result set. The following-colored tables illustration will help us to understand the joined tables data matching in the query. The yellow-colored rows specify matched data between onlinecustomers and orders. On the other hand, only the blue colored rows exist in the sales tables so the query result will be blue colored rows: The result of the query will look like this:
PRACTICAL 12: USE OF SUB QUERIES. Solution: 1. A subquery is a SQL query nested inside a larger query. • A subquery may occur in : o - A SELECT clause o - A FROM clause o - A WHERE clause • The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. • A subquery is usually added within the WHERE Clause of another SQL SELECT statement. • You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, or ALL. • A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select. • The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query. You can use a subquery in a SELECT, INSERT, DELETE, or UPDATE statement to perform the following tasks: • Compare an expression to the result of the query. • Determine if an expression is included in the results of the query. • Check whether the query selects any rows. Syntax :
• The subquery (inner query) executes once before the main query (outer query) executes. • The main query (outer query) uses the subquery result. SQL Subqueries Example : In this section, you will learn the requirements of using subqueries. We have the following two tables 'student' and 'marks' with common field 'StudentID'. student marks Now we want to write a query to identify all students who get better marks than that of the student who's StudentID is 'V002', but we do not know the marks of 'V002'. - To solve the problem, we require two queries. One query returns the marks (stored in Total_marks field) of 'V002' and a second query identifies the students who get better
marks than the result of the first query. First query: SELECT * FROM `marks` WHERE studentid = 'V002'; Copy Query result: The result of the query is 80. - Using the result of this query, here we have written another query to identify the students who get better marks than 80. Here is the query : Second query: SELECT a.studentid, a.name, b.total_marks FROM student a, marks b WHERE a.studentid = b.studentid AND b.total_marks >80; Copy Query result:
Above two queries identified students who get the better number than the student who's StudentID is 'V002' (Abhay). You can combine the above two queries by placing one query inside the other. The subquery (also called the 'inner query') is the query inside the parentheses. See the following code and query result : SQL Code: SELECT a.studentid, a.name, b.total_marks FROM student a, marks b WHERE a.studentid = b.studentid AND b.total_marks > (SELECT total_marks FROM marks WHERE studentid = 'V002'); Copy Query result:
Search
