Scribd
Upload
310 views3 pages

SQL Module 5 Assignment Overview

The document describes a module 5 SQL assignment. The tasks are to: 1. Order a dataset by amount descending 2. Create and populate two tables with employee data 3. Apply the union operator to combine the tables 4. Apply the intersect operator to find matching rows 5. Apply the except operator to find rows only in the first table Solutions for each task are provided using SQL queries and showing the results.

Uploaded by

Anita Balakrishnan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
310 views3 pages

SQL Module 5 Assignment Overview

The document describes a module 5 SQL assignment. The tasks are to: 1. Order a dataset by amount descending 2. Create and populate two tables with employee data 3. Apply the union operator to combine the tables 4. Apply the intersect operator to find matching rows 5. Apply the except operator to find rows only in the first table Solutions for each task are provided using SQL queries and showing the results.

Uploaded by

Anita Balakrishnan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
  • Problem Statement
  • Solutions
  • Tasks

Module-5 Assignment

Problem Statement:
You have successfully cleared your 4th semester. In the 5th semester you will work with group by, having
by clauses and set operators

Tasks to be done:

1. Arrange the ‘Orders’ dataset in decreasing order of amount


2. Create a table with name ‘Employee_details1’ and comprising of these columns – ‘Emp_id’,
‘Emp_name’, ‘Emp_salary’. Create another table with name ‘Employee_details2’, which
comprises of same columns as first table.
3. Apply the union operator on these two tables
4. Apply the intersect operator on these two tables
5. Apply the except operator on these two tables

Solutions

1.
SELECT * FROM orders
ORDER BY amount DESC

RESULT

Order_id order_date amount customer_id


1004 2021-10-18 200 4
1002 2021-10-16 150 2
1003 2021-10-17 100 3
1001 2021-10-15 100 1

2.

CREATE TABLE Employee_details1 (


Emp_id INT,"Emp_name" VARCHAR (20), Emp_salary INT
)

/2

ANITA BALAKRISHNAN EMAIL annkallid@[Link] MODULE 5 ASSIGNMENT - SQL


-2-

INSERT INTO Employee_details1


VALUES
(1, 'John', 10000),
(2, 'James', 15000),
(3, 'Ann', 25000),
(4, 'Sara', 20000),
(5, 'Laura', 35000)

CREATE TABLE Employee_details2


(Emp_id INT,"Emp_name" VARCHAR (20), Emp_salary INT)

INSERT INTO Employee_details2


VALUES
(1, 'Jimmy', 40000),
(2, 'James', 15000),
(3, 'Ann', 25000),
(4, 'Susy', 30000),
(5, 'Lennie', 35000)

3.

SELECT * FROM Employee_details1


UNION
SELECT * FROM Employee_details2

RESULT

Emp_id Emp_name Emp_salary


1 Jimmy 40000
1 John 10000
2 James 15000
3 Ann 25000
4 Sara 20000
4 Susy 30000
5 Laura 35000
5 Lennie 35000

4.
SELECT * FROM Employee_details1
INTERSECT
SELECT * FROM Employee_details2

RESULT

Emp_id Emp_name Emp_salary


2 James 15000
3 Ann 25000

/3

ANITA BALAKRISHNAN EMAIL annkallid@[Link] MODULE 5 ASSIGNMENT - SQL


-3-

5.

SELECT * FROM Employee_details1


EXCEPT
SELECT * FROM Employee_details2

RESULT

Emp_id Emp_name Emp_salary


1 John 10000
4 Sara 20000
5 Laura 35000

ANITA BALAKRISHNAN EMAIL annkallid@[Link] MODULE 5 ASSIGNMENT - SQL

Common questions

Powered by AI

Set operators like union, intersect, and except are essential in SQL as they enhance data analysis by allowing the combination and comparison of multiple datasets effectively. The union operator helps in compiling datasets from different sources without duplicates; intersect identifies common data points, crucial for pinpointing overlaps; except helps isolate unique entries. These operations enable complex queries and data validation needed for comprehensive data analysis and integration efforts .

The union operator in SQL combines the result sets of two or more queries, eliminating duplicate records, to provide a comprehensive list of distinct entries found in any of the tables. When applied to the 'Employee_details1' and 'Employee_details2', it results in a dataset that includes unique employees from both tables. In contrast, the intersect operator returns only the rows that exist in both tables, identifying common elements, resulting in a dataset of employees who appear in both tables, such as 'James' and 'Ann' .

Including constraints like 'NOT NULL' ensures that no null values are entered for the specified columns, thus maintaining complete and reliable records, while 'UNIQUE' enforces the uniqueness of the column values, preventing duplicate entries. In tables like 'Employee_details1' and 'Employee_details2', these constraints would enhance data integrity by guaranteeing that every employee has a unique ID and all required fields have values, aiding in accurate data retrieval and analysis .

Ordering data is significant in querying databases as it determines the sequence in which records are retrieved, impacting the interpretation and analysis of data. In the given task of arranging the 'Orders' dataset, ordering by amount in descending order ensures that the largest transactions are prioritized and visible first, which can be essential for financial analysis or auditing .

Using incorrect data types in SQL table definitions can lead to data integrity issues, inefficient storage, and erroneous query results. For 'Employee_details1' and 'Employee_details2', inappropriate data types could result in invalid sorting or computational errors, especially if numerical operations are attempted on non-numeric fields, emphasizing the need for accurate data type assignment to ensure reliable data management and retrieval .

The 'except' SQL operator is used to subtract the results of one query from another, essentially filtering out common entries found in both datasets. When applied to 'Employee_details1' and 'Employee_details2', it highlights entries unique to the first dataset, in this case, employees 'John', 'Sara', and 'Laura', who are not present in the second table .

Sorting datasets based on different columns affects how information is processed and visualized, influencing data interpretation outcomes. Key considerations include the intended use of sorted data, such as prioritizing records by financial amounts for budget assessments or by date for chronological trends. It's crucial to ensure sorting supports the data's intent, accentuates significant patterns, and aligns with analytical goals .

Managing employee data across multiple tables can offer benefits like reduced redundancy through normalization and specialized data handling for different departments or purposes. However, challenges include increased complexity in querying data, potential for data inconsistency, and the need for more intricate joining or set operations to produce comprehensive datasets, as seen with the use of union and intersect operations on 'Employee_details1' and 'Employee_details2' .

To improve the scalability of the employee database, one might recommend normalizing the tables further to eliminate redundancy, incorporating indexing for faster queries, and partitioning tables for better performance with large datasets. Introducing constraints such as foreign keys can enhance referential integrity and using a distributed database system can aid in managing increased load and geographical distribution .

When using both union and intersect operators, one must consider the datasets' semantic consistency, ensuring that the columns and data types match exactly across datasets. It's also vital to anticipate the logical outcomes of these operations, which involves understanding that union will combine datasets while intersect will filter to commonalities. Avoiding conflicting results requires aligning datasets on key attributes and considering the dataset's business logic, such as avoiding unintended redundancies or omitting critical commonalities, necessitating a structured query approach .

You might also like