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) REFERENCES user_account(id),

    CONSTRAINT FK_role FOREIGN KEY (role_id) REFERENCES role(id)

);



drop table user_has_role

----

-- creating table user_has_status

create table user_has_status(id int primary key,

status_start_time timestamp,

status_end_time datetime not null,

user_account_id int foreign key references user_account(id),

status_id int foreign key references status(id)

);


/*Insert data into each of the above tables. With at least two rows in each of

the tables. Make sure that you have created respective foreign keys.*/


insert into role (id,role_name)

select 1,'user1' union all

select 2,'user2' ;


select * from role

insert into status(id,status_name,is_user_working)

select 1,'new status',1 union all

select 2,'new status',1 ;


select * from status;


insert into user_account(id,user_name,email,password,password_salt,password_hash_algorithm)

select 1,'abc','abc@xys.com','abc','abcs','abcha' union all

select 2,'def','def@xys.com','yth','yths','defhpa';

select * from user_account;


insert into user_has_role(id,role_end_time,user_account_id,role_id)

select 1,getdate()+10,1,1 union all

select 2,getdate()+25,2,2     ;   


select * from user_has_role;


insert into user_has_status(id,status_end_time,user_account_id,status_id)

select 1,getdate()+10,1,1 union all

select 2,getdate()+25,2,2;


select * from user_has_status;


--Delete all the data from each of the tables

truncate table user_has_status;

select * from user_has_status;


truncate table user_has_role

--remove foreign key

alter table user_has_role drop constraint FK_role

alter table user_has_role drop constraint FK_user_account

truncate table role

select * from user_has_role


--remove foreign key

alter table user_has_status drop constraint FK__user_has___statu__4222D4EF -- system generated FK name since we didnt given

alter table user_has_status drop constraint FK__user_has___user___412EB0B6


truncate table status


truncate table user_account

select * from user_account







Comments

Post a Comment

Popular posts from this blog

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...
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,  ...
Read more

SQL_chapter1_assignment

-
 --1.Find out the selling cost AVG for packages developed in Pascal. Select AVG(SCOST) as [Average selling cost] From SOFTWARE Where DEVELOPIN='PASCAL'; --2.Display Names, Ages of all Programmers. select pname as Names, DATEDIFF(YY,[DOB],GETDATE()) as [Age] from programmer; --3.Display the Names of those who have done the DAP Course. Select PNAME as Names, COURSE from STUDIES where COURSE = 'DAP'; --4.Display the Names and Date of Births of all Programmers Born in January. Select PNAME as Names,DOB, DATENAME(MM, DOB) as [Month] from PROGRAMMER where DATENAME(MM, DOB) = 'January' --5.Display the Details of the Software Developed by Ramesh select * from SOFTWARE where PNAME='Ramesh' --6. Display the Details of Packages for which Development Cost have been recovered. select * , (SCOST*SOLD) as [Total Sale] from SOFTWARE  where (SCOST*SOLD) > DCOST --7. Display the details of the Programmers Knowing C select pname from PROGRAMMER where PROF1='C' o...
Read more