Scribd
Upload
30 views16 pages

SQL Assignment Solutions and Queries

The document provides SQL assignment solutions, including the creation of various tables with primary keys, performing DML operations, and executing queries with aggregate functions. It covers topics such as integrity constraints, views, joins, and string operations. Additionally, it demonstrates user creation and permission granting in SQL.

Uploaded by

Arif choudhary
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
30 views16 pages

SQL Assignment Solutions and Queries

The document provides SQL assignment solutions, including the creation of various tables with primary keys, performing DML operations, and executing queries with aggregate functions. It covers topics such as integrity constraints, views, joins, and string operations. Additionally, it demonstrates user creation and permission granting in SQL.

Uploaded by

Arif choudhary
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

SQL Assignment Solutions

1. Create Employee and Course tables with a primary key in each.


CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Department VARCHAR(50)
);

CREATE TABLE Course (


CourseID INT PRIMARY KEY,
CourseName VARCHAR(50)
);
SQL Assignment Solutions

2. Create a table and perform all DML commands


CREATE TABLE Sample (
ID INT PRIMARY KEY,
Name VARCHAR(50)
);

INSERT INTO Sample VALUES (1, 'Alice');


UPDATE Sample SET Name = 'Alicia' WHERE ID = 1;
DELETE FROM Sample WHERE ID = 1;
SELECT * FROM Sample;
SQL Assignment Solutions

3. Create a department table with all integrity constraints.


CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50) UNIQUE NOT NULL,
Location VARCHAR(50),
Budget DECIMAL(10, 2) CHECK (Budget > 0)
);
SQL Assignment Solutions

4. Queries on the Client table


-- a
SELECT * FROM Client WHERE BalDue BETWEEN 1000 AND 2000;

-- b
SELECT MIN(BalDue), MAX(BalDue) FROM Client;

-- c
SELECT Name, City, State FROM Client WHERE State != 'Maharashtra';
SQL Assignment Solutions

5. Create any two views and retrieve data from them


CREATE VIEW ViewClient AS
SELECT Name, City FROM Client;

CREATE VIEW ViewDue AS


SELECT Name, BalDue FROM Client WHERE BalDue > 1000;

-- Retrieve
SELECT * FROM ViewClient;
SELECT * FROM ViewDue;
SQL Assignment Solutions

6. Create a Product table and perform any two DML commands


CREATE TABLE Product (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(50),
Price DECIMAL(10, 2)
);

INSERT INTO Product VALUES (1, 'Pen', 10.00);


UPDATE Product SET Price = 12.00 WHERE ProductID = 1;
SQL Assignment Solutions

7. Perform queries using aggregate functions


SELECT COUNT(*) AS TotalClients FROM Client;
SELECT AVG(BalDue) AS AverageDue FROM Client;
SQL Assignment Solutions

8. Run queries
-- a
SELECT * FROM Client WHERE City LIKE 'M%';

-- b
SELECT * FROM Client WHERE State = 'Maharashtra' AND BalDue > 10000;
SQL Assignment Solutions

9. Perform updates and queries


-- a
UPDATE Client SET City = 'Bangalore', PinCode = '570098', State = 'Karnataka' WHERE
Client_No = 'C0005';

-- b
SELECT * FROM Sales WHERE YEAR(PurchaseDate) = 2018;
SQL Assignment Solutions

10. Create Student and Furniture tables with a primary key


CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50)
);

CREATE TABLE Furniture (


FurnitureID INT PRIMARY KEY,
FurnitureName VARCHAR(50)
);
SQL Assignment Solutions

11. Perform any two DML operations on the student table


INSERT INTO Student VALUES (1, 'John');
UPDATE Student SET StudentName = 'Johnny' WHERE StudentID = 1;
SQL Assignment Solutions

12. Show one example of primary key and foreign key relationship
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
EmpID INT,
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);
SQL Assignment Solutions

13. Perform any 5 string operations


SELECT UPPER('hello') AS UpperCase;
SELECT LOWER('WORLD') AS LowerCase;
SELECT LENGTH('Test') AS Length;
SELECT SUBSTRING('Database', 1, 4) AS SubStr;
SELECT CONCAT('Hello', ' ', 'World') AS FullStr;
SQL Assignment Solutions

14. Create a new user, grant permissions on a table to the user, and verify the granted permissions
CREATE USER new_user IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON Product TO new_user;
SHOW GRANTS FOR new_user;
SQL Assignment Solutions

15. Perform Join Operations


-- Right Outer Join
SELECT * FROM Employee RIGHT OUTER JOIN Department ON [Link] =
[Link];

-- Left Outer Join


SELECT * FROM Employee LEFT OUTER JOIN Department ON [Link] =
[Link];
SQL Assignment Solutions

16. Create 2 tables and insert 7 values in each


CREATE TABLE Table1 (
ID INT PRIMARY KEY,
Name VARCHAR(50)
);

CREATE TABLE Table2 (


ID INT PRIMARY KEY,
Description VARCHAR(50)
);

INSERT INTO Table1 VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F'),(7,'G');


INSERT INTO Table2 VALUES (1,'X'),(2,'Y'),(3,'Z'),(4,'W'),(5,'P'),(6,'Q'),(7,'R');

Common questions

Powered by AI

DML operations modify data within tables, directly impacting database state. For the Product table, an INSERT operation adds new products, altering the database to include these entries. An UPDATE operation changes existing data, like price adjustments, impacting real-time product information. Together, these operations provide dynamic data changes to meet business needs without altering the database schema .

Aggregate functions perform calculations on data sets, offering insights through summarization. In the Client table, COUNT(*) provides total client count, essential for market size analysis. AVG(BalDue) delivers average balance due, crucial for assessing client credit risk. These functions enable data-driven strategies in finance, marketing, and operations by delivering pivotal quantitative insights .

String operations in SQL enable detailed text data manipulation. Functionality like UPPER and LOWER standardizes text case while SUBSTRING extracts specific string parts, aiding data normalization and analysis. CONCAT supports building cumulative data strings from separate fields. These operations enhance text processing efficiency, crucial for data cleansing and formatting tasks .

Integrity constraints ensure data accuracy and consistency. In the Department table, a PRIMARY KEY constraint on DeptID guarantees unique identifiers. The UNIQUE NOT NULL constraint on DeptName ensures no two departments have the same name and no name can be missing, which prevents ambiguities. The CHECK constraint on Budget ensures values are positive, maintaining data validity regarding department budgets. These constraints collectively enhance the data integrity of the Department table .

Constraints in SQL ensure that database entries comply with integrity rules, preventing data anomalies. The Sample table uses a PRIMARY KEY constraint on ID to prevent duplicate entries, ensuring each record is distinct. This constraint maintains data uniformity, vital for reliable data retrieval and accurate database operations .

The primary key uniquely identifies each record, avoiding duplication and ensuring consistent data retrieval. In the Employee table, EmpID is a primary key that uniquely differentiates each employee. In the Orders table, OrderID is the primary key, ensuring each order is distinct, while EmpID serves as a foreign key linking orders to employees, enforcing referential integrity .

User-specific permissions control access and ensure security in SQL databases. Granting new_user SELECT and INSERT permissions on the Product table restricts access to safe operations, preventing unauthorized data alterations or deletions. This limitation safeguards data integrity and enforces security policies crucial for managing sensitive business data .

Foreign keys define relationships between tables, maintaining data integrity across them. In the Orders table, EmpID as a foreign key references Employee table's EmpID, ensuring all orders map to existing employees. This prevents orphan records, guaranteeing that any order has a valid employee associated, thus maintaining referential integrity .

SQL views streamline complex queries into simpler, reusable queries and enhance data abstraction. ViewClient simplifies accessing client names and cities, protecting underlying schema details. ViewDue filters clients with a BalDue greater than 1000, abstracting complex checks into a single query. This encapsulation eases maintenance and security by limiting direct access to tables .

Join operations integrate data across tables, setting the context for comprehensive data analysis. In the Right Outer Join between Employee and Department, all departments are listed with possible employee assignments, showing departments even without employees. Conversely, the Left Outer Join lists all employees with their department details, ensuring each employee is represented even if the department data is missing. These operations provide complete data views for decision-making .

You might also like