Posts

Mini Project

-
create database Project_3; use Project_3; -- creating table <role> create table role(id int primary key, role_name varchar(100)); -- creating status table create table status (id int primary key,status_name varchar(100),is_user_working bit) -- creating user_account table create table user_account(id int primary key, user_name varchar(100), email varchar(250), password varchar(200), password_salt varchar(50) not null, password_hash_algorithm varchar(50)); --- creating table user_has_role create table user_has_role(id int primary key, role_start_time timestamp, role_end_time datetime not null, user_account_id int foreign key references user_account(id), role_id int foreign key references role(id) ); ----- CREATE TABLE user_has_role (     id INT PRIMARY KEY,     role_start_time TIMESTAMP,     role_end_time DATETIME NOT NULL,     user_account_id INT,     role_id INT,     CONSTRAINT FK_user_account FOREIGN KEY (user_account_id...
Post a Comment
Read more

Case study

-
create database casestudy; use casestudy; ----------------------------------------------------------------------------------- select top 10 * from fact select top 10 * from location select top 10 * from product ---------------------------------------------------------------------------------- /* Problem Statement:  You are a database administrator. You want to use the data to answer a few  questions about your customers, especially about the sales and profit coming  from different states, money spent in marketing and various other factors such as  COGS (Cost of Goods Sold), budget profit etc. You plan on using these insights  to help find out which items are being sold the most. You have been provided  with the sample of the overall customer data due to privacy issues. But you hope  that these samples are enough for you to write fully functioning SQL queries to  help answer the questions.*/ ---------------------------------------------------------...
Post a Comment
Read more

Assignment Module 5&6

-
create database assignment3; use assignment3; /*You work for a data analytics company, and your client is a food delivery platform similar to Jomato. They have provided you with a dataset containing information about various restaurants in a city. Your task is to analyze this dataset using SQL queries to extract valuable insights and generate reports for your client.*/ -------------------------------------------------------------------------------------------------------------------- -- 1.Create a stored procedure to display the restaurant name, type and cuisine where the table booking is not zero. create procedure sp_zero_booking as select restaurantName,restaurantType,CuisinesType from jomato  where tablebooking ='No' go; exec sp_zero_booking; -------------------------------------------------------------------------------------------------------------- -- 2.Create a transaction and update the cuisine type ‘Cafe’ to ‘Cafeteria’. Check the result and rollback it. --Create a tra...
Post a Comment
Read more

Assignment Module 3&4

-
create database assignment2 use assignment2 select * from jomato -------------------------------------------------------------------------------------------------------- /* Dataset: Jomato  About the dataset:  You work for a data analytics company, and your client is a food delivery platform similar to  Jomato. They have provided you with a dataset containing information about various  restaurants in a city. Your task is to analyze this dataset using SQL queries to extract valuable  insights and generate reports for your client. */ -----------------------------------------------------------------------------------------------------   --Tasks to be performed:  -- 1.Create a user-defined functions to stuff the Chicken into ‘Quick Bites’. Eg: ‘Quick Chicken Bites’  create function fn_modify_restaurant_type(  @oldtype nvarchar(100),  @newtype nvarchar(100)  )  returns table  as  return ( select Orderid,  ...
Post a Comment
Read more

Assignment module 1&2

-
 create database assignment1; use assignment1; /*Problem Statement:  ABC Fashion is a leading retailer with a vast customer base and a team of dedicated sales  representatives. They have a Sales Order Processing System that helps manage customer  orders and interactions.*/  -- Salesman table creation  CREATE TABLE Salesman (  SalesmanId INT,  Name VARCHAR(255),  Commission DECIMAL(10, 2),  City VARCHAR(255),  Age INT  );  --Salesman table record insertion  INSERT INTO Salesman (SalesmanId, Name, Commission, City, Age)  VALUES  (101, 'Joe', 50, 'California', 17),  (102, 'Simon', 75, 'Texas', 25),  (103, 'Jessie', 105, 'Florida', 35),  (104, 'Danny', 100, 'Texas', 22),  (105, 'Lia', 65, 'New Jersey', 30);  -- Customer table creation  CREATE TABLE Customer (  SalesmanId INT,  CustomerId INT,  CustomerName VARCHAR(255),  PurchaseAmount INT,  );  -- Cust...
Post a Comment
Read more

SQL_Chapter4_ASSIGNMENT

-
 ---1. What is the cost of the costliest software development in Basic? select TITLE, SCOST from SOFTWARE where SCOST=(select MAX(SCOST) from SOFTWARE) --2. Display the Cost of Package Developed By each Programmer select PNAME, title, dcost, sum(dcost) over(partition by pname) as [total cost of packages] from SOFTWARE --3. Who are the Programmers who celebrate their Birthdays during the Current Month? select PNAME, month(DOB) as [month] from PROGRAMMER where month(DOB)=MONTH(GETDATE()) select PNAME, datename(mm, DOB) as [month] from PROGRAMMER where DATENAME(MM, DOB)= DATENAME(mm, getdate()) --4. Display the sales values of the Packages Developed by each Programmer? select distinct PNAME, sum(SCOST*SOLD) over(partition by pname) as [sales values] from SOFTWARE --5. Display the Number of Packages sold by Each Programmer select distinct PNAME, COUNT(title) over(partition by pname) as[packages sold] from SOFTWARE select pname, COUNT(title) from SOFTWARE group by PNAME ---6. Display ea...
Post a Comment
Read more

SQL-Chapter3-Assignment

-
 --1. How many Programmers Don’t know PASCAL and C select PNAME AS [Programmers] from PROGRAMMER where PROF1 not in ('PASCAL', 'C') AND PROF2 not in ('PASCAL', 'C') --2. Display the details of those who don’t know Clipper, COBOL or PASCAL select *  from PROGRAMMER where PROF1 NOT IN('Clipper','COBOL','PASCAL')  AND PROF2 NOT IN('Clipper','COBOL','PASCAL') --3. Display each language name with AVG Development Cost, AVG Selling Cost and AVG Price per Copy select DEVELOPIN as [software names], AVG(dcost) as [avg development cost], AVG(scost)as [avg selling cost], AVG(SCOST/nullif(SOLD,0)) as [Average price] from SOFTWARE group by DEVELOPIN --4.  List the programmer names (from the programmer table) and No. Of Packages each has developed.  select P.PNAME AS Programmers, count(S.title) as [no of packages] from PROGRAMMER p  inner join SOFTWARE s on p.PNAME=s.PNAME group by p.PNAME o...
Post a Comment
Read more