search

uday0097 / SQLAssignmentsSubmission

Here you can submit your SQL Assignments answers
0 stars 0 forks source link

Assignment: SQL Comparison, Logical Operators, and Wildcard Characters #2

Open kanchanpal710 opened 5 months ago

kanchanpal710 commented 5 months ago

select * from products;

INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (1, 'Laptop', 'Electronics', 800); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (2, 'Smartphone', 'Electronics', 600); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (3, 'Tablet', 'Electronics', 400); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (4, 'Headphones', 'Electronics', 100); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (5, 'Television', 'Electronics', 1200); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (6, 'Keyboard', 'Electronics', 50); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (7, 'Mouse', 'Electronics', 30); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (8, 'Printer', 'Electronics', 150); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (9, 'Camera', 'Electronics', 300); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (10, 'Speaker', 'Electronics', 80);

INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (11, 'Dress Shirt', 'Clothing', 35); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (12, 'Jeans', 'Clothing', 50); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (13, 'Sneakers', 'Clothing', 70); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (14, 'Dress', 'Clothing', 120); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (15, 'Socks', 'Clothing', 5); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (16, 'T-shirt', 'Clothing', 20); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (17, 'Skirt', 'Clothing', 40); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (18, 'Jacket', 'Clothing', 90); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (19, 'Scarf', 'Clothing', 25); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (20, 'Hat', 'Clothing', 15);

INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (21, 'Coffee Maker', 'Appliances', 60); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (22, 'Microwave', 'Appliances', 120); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (23, 'Blender', 'Appliances', 40); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (24, 'Toaster', 'Appliances', 25); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (25, 'Iron', 'Appliances', 35); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (26, 'Vacuum Cleaner', 'Appliances', 150); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (27, 'Hair Dryer', 'Appliances', 50); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (28, 'Food Processor', 'Appliances', 90); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (29, 'Rice Cooker', 'Appliances', 70); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (30, 'Electric Kettle', 'Appliances', 30);

INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (31, 'Bookshelf', 'Furniture', 200); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (32, 'Sofa', 'Furniture', 500); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (33, 'Dining Table', 'Furniture', 300); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (34, 'Bed', 'Furniture', 700); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (35, 'Desk', 'Furniture', 250); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (36, 'Wardrobe', 'Furniture', 400); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (37, 'Coffee Table', 'Furniture', 150); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (38, 'TV Stand', 'Furniture', 180); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (39, 'Cabinet', 'Furniture', 220); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (40, 'Nightstand', 'Furniture', 100);

INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (41, 'Backpack', 'Accessories', 40); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (42, 'Wallet', 'Accessories', 25); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (43, 'Watch', 'Accessories', 100); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (44, 'Belt', 'Accessories', 30); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (45, 'Hat', 'Accessories', 20); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (46, 'Sunglasses', 'Accessories', 50); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (47, 'Necklace', 'Accessories', 60); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (48, 'Earrings', 'Accessories', 35); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (49, 'Gloves', 'Accessories', 15); INSERT INTO Products (ProductID, ProductName, Category, Price) VALUES (50, 'Scarf', 'Accessories', 25);

select * from products;

Retrieve all products with a price greater than $50.

select productname from products where price<50;

Retrieve products in the 'Electronics' category.

select productname from products where category='Electronics';

Retrieve products with a name starting with 'A'.

select * from products where productname like'A%'; ===============NOT GETTING OUTPUT===

Retrieve products with a name containing 'Phone'.

select productname from products where productname='phone';

commit;

select * from products;

commit;

Retrieve products with a price between $50 and $100.

select productname from products where price between 50 and 100;

Retrieve products with a name starting with 'S' and in the 'Clothing' category.

select productname from products where category='clothing' like s%;

select from products where productname like s% and category='clothing'; ====GETTING ERROR====

Retrieve products with a price less than or equal to $30 or in the 'Accessories' category.

select productname from products where price<=30 or Category='Accessories';

uday0097 commented 4 months ago

Good keep going

JyothiPinnam commented 4 months ago

Create table Employees2 (EmployeeID int Primary key , FirstName Varchar(20), LastName Varchar(20), Department varchar(20), Salary int);

insert into Employees2 (EmployeeID ,FirstName ,LastName ,Department ,Salary) values (101,'Sita','Mitra','Finance',12000); insert into Employees2 (EmployeeID ,FirstName ,LastName ,Department ,Salary) values (102,'Gita','Mitra','Finance',7000); insert into Employees2 (EmployeeID ,FirstName ,LastName ,Department ,Salary) values (103,'Neha','Chowdary','Marketing',5000); insert into Employees2 (EmployeeID ,FirstName ,LastName ,Department ,Salary) values (104,'Mounika','Injeti','Hr',15000); insert into Employees2 (EmployeeID ,FirstName ,LastName ,Department ,Salary) values (105,'Madhu','Chevva','Hr',8000); insert into Employees2 (EmployeeID ,FirstName ,LastName ,Department ,Salary) values (106,'Rahul','Kishav','Marketing',6000);

Select * from Employees2;

----Q.Retrieve the names and salaries of all employees earning more than $5000

select FirstName, LastName,Salary from Employees2 where Salary>5000;

----Q.Retrieve the count of employees in each department.

select Department,count(FirstName) as 'Count of employees'from employees2 group by Department ;

-----Update the salary of an employee with a specific EmployeeID.

update Employees2 set salary =9000 where EmployeeID = 106;

select * from Employees2;

---Delete a record from the Employees table based on a specific condition.

delete employees2 where EmployeeID =103;

Create table Products1 ( ProductID int Primary Key, ProductName Varchar (20), Category Varchar (20), Price int);

INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (1, 'Laptop', 'Electronics', 800); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (2, 'Smartphone', 'Electronics', 600); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (3, 'Tablet', 'Electronics', 400); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (4, 'Headphones', 'Electronics', 100); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (5, 'Television', 'Electronics', 1200); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (6, 'Keyboard', 'Electronics', 50); INSERT INTO Products1(ProductID, ProductName, Category, Price) VALUES (7, 'Mouse', 'Electronics', 30); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (8, 'Printer', 'Electronics', 150); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (9, 'Camera', 'Electronics', 300); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (10, 'Speaker', 'Electronics', 80);

INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (11, 'Dress Shirt', 'Clothing', 35); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (12, 'Jeans', 'Clothing', 50); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (13, 'Sneakers', 'Clothing', 70); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (14, 'Dress', 'Clothing', 120); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (15, 'Socks', 'Clothing', 5); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (16, 'T-shirt', 'Clothing', 20); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (17, 'Skirt', 'Clothing', 40); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (18, 'Jacket', 'Clothing', 90); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (19, 'Scarf', 'Clothing', 25); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (20, 'Hat', 'Clothing', 15);

INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (21, 'Coffee Maker', 'Appliances', 60); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (22, 'Microwave', 'Appliances', 120); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (23, 'Blender', 'Appliances', 40); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (24, 'Toaster', 'Appliances', 25); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (25, 'Iron', 'Appliances', 35); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (26, 'Vacuum Cleaner', 'Appliances', 150); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (27, 'Hair Dryer', 'Appliances', 50); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (28, 'Food Processor', 'Appliances', 90); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (29, 'Rice Cooker', 'Appliances', 70); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (30, 'Electric Kettle', 'Appliances', 30);

INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (31, 'Bookshelf', 'Furniture', 200); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (32, 'Sofa', 'Furniture', 500); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (33, 'Dining Table', 'Furniture', 300); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (34, 'Bed', 'Furniture', 700); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (35, 'Desk', 'Furniture', 250); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (36, 'Wardrobe', 'Furniture', 400); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (37, 'Coffee Table', 'Furniture', 150); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (38, 'TV Stand', 'Furniture', 180); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (39, 'Cabinet', 'Furniture', 220); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (40, 'Nightstand', 'Furniture', 100);

INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (41, 'Backpack', 'Accessories', 40); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (42, 'Wallet', 'Accessories', 25); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (43, 'Watch', 'Accessories', 100); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (44, 'Belt', 'Accessories', 30); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (45, 'Hat', 'Accessories', 20); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (46, 'Sunglasses', 'Accessories', 50); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (47, 'Necklace', 'Accessories', 60); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (48, 'Earrings', 'Accessories', 35); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (49, 'Gloves', 'Accessories', 15); INSERT INTO Products1 (ProductID, ProductName, Category, Price) VALUES (50, 'Scarf', 'Accessories', 25);

select * from Products1;

---Retrieve all products with a price greater than $50.

select * from Products1 where Price>50;

----Retrieve products in the 'Electronics' category. select * from Products1 where Category='Electronics';

----Retrieve products with a name starting with 'A'. select * from Products1 where ProductName like'A%';

----Retrieve products with a name containing 'Phone'. select from Products1 where ProductName ='Phone'; ----Retrieve products with a price between $50 and $100. select from Products1 where Price between 50 and 100;

---Retrieve products with a name starting with 'S' and in the 'Clothing' category. select from Products1 where ProductName like 'S%' and Category='Clothing'; ---Retrieve products with a price less than or equal to $30 or in the 'Accessories' category. select from Products1 where Price<=30 or Category='Accessories';