ayesha sulaiman bcs 4 (LIBRARY DATABASE QUREIS)

create table BOOKS
(
Book_id INT UNIQUE,
Title VARCHAR(500),
Publisher_name VARCHAR(500)
);

insert into BOOKS values (1,’The Lost Tribe’,'Pak Publishers’)
insert into BOOKS values (2,’Da Vinci Code’,'Shalimar Publishers’)
insert into BOOKS values (3,’Thirst Crow’,'Kinders’)
insert into BOOKS values (4,’Reality of Politics’,'Pak Publishers’)
insert into BOOKS values (5,’The Senseless’,'Bright Publications’)

select * from BOOKS

create table Book_Author
(
Book_id int,
Author_name varchar(50)
);

insert into Book_Author values (1,’Stephen King’)
insert into Book_Author values (2,’Hassan Sardaar’)
insert into Book_Author values (3,’Hamid Khan’)
insert into Book_Author values (4,’Stephen King’)
insert into Book_Author values (5,’Aida Nisar’)

select * from Book_Author

create table PUBLISHER
(
Name varchar(50) UNIQUE,
Address varchar(50),
Phone varchar(50)
);

insert into PUBLISHER values (‘dogar Publishers’,’130 defense phase 5, Lahore’,’0333-1234567′)
insert into PUBLISHER values (‘azeem Publishers’,'Urdu Bazaar, Lahore’,’0333-4348113′)
insert into PUBLISHER values (‘Kinders’,'cantt, Lahore’,’0321-4898641′)
insert into PUBLISHER values (‘ferozsons Publications’,’765 gulberg 3 Lahore’,’0300-6767676′)

select * from PUBLISHER

create table Book_Copies
(
Book_id int,
Branch_id int,
No_Of_Copies int
);

insert into Book_Copies values (1,8,10)
insert into Book_Copies values (2,7,20)
insert into Book_Copies values (3,9,30)
insert into Book_Copies values (4,6,15)
insert into Book_Copies values (5,5,9)

select * from Book_Copies

create table Book_Loan
(
Book_id int,
Branch_id int,
Card_no int,
Date_out varchar(500),
Due_date varchar(500),
status varchar(500)
);

insert into Book_Loan values (1,8,9,’18-5-13′,’29-5-13′,’pending’)
insert into Book_Loan values (2,7,8,’10-4-13′,’17-4-13′,’done’)
insert into Book_Loan values (3,9,7,’13-5-13′,’20-5-13′, ‘pending’)
insert into Book_Loan values (4,6,6,’14-5-13′,’21-5-13′,’pending’)
insert into Book_Loan values (5,5,5,’12-5-13′,’19-5-13′,’done’)

select * from Book_Loan

create table Library_Branch
(
Branch_id int UNIQUE,
Branch_name varchar(50),
Address varchar(50)
);

insert into Library_Branch values (8,’urdu bazar Branch’,'canal view, Lahore’)
insert into Library_Branch values (7,’Thokar Branch’,’130 defense phase 5, Lahore’)
insert into Library_Branch values (9,’raiwand Market Branch’,'cantt, Lahore’)
insert into Library_Branch values (6,’shalimar bagh Branch’,'gulberg 3, Lahore’)
insert into Library_Branch values (5,’Defence Branch’,’47 zz Defence, Lahore’)

select * from Library_Branch

create table Borrower
(
Card_no int UNIQUE,
Name varchar(50),
Address varchar(50),
Phone varchar(50)
);

insert into Borrower values (9,’maryam’,’564-P Defence, Lahore’,’03001234567′)
insert into Borrower values (8,’isma’,'cantt, Lahore’,'o333-4348113′)
insert into Borrower values (7,’ayesha’,'raiwand Road, Lahore’,’0331-4898641′)
insert into Borrower values (6,’fatima’,'multan Road, Lahore’,’0323-4612994′)
insert into Borrower values (5,’amna’,’44 samanabad, Lahore’,’0334-7593601′)

select * from Borrower

select No_Of_Copies
from BOOKS INNER JOIN Book_Copies
on BOOKS.Book_id = Book_Copies.Book_id INNER JOIN Library_Branch
on Book_Copies.Branch_id = Library_Branch.Branch_id
where BOOKS.Title = ‘The Lost Tribe’ and Library_Branch.Branch_name = ‘Defence Branch’

select Branch_name,SUM(No_Of_Copies)
from BOOKS INNER JOIN Book_Copies
on BOOKS.Book_id = Book_Copies.Book_id INNER JOIN Library_Branch
on Book_Copies.Branch_id = Library_Branch .Branch_id
where BOOKS.Title = ‘The Lost Tribe’
group by Library_Branch .Branch_name

select Title,Name
from BOOKS INNER JOIN Book_Loan
on BOOKS.Book_id = Book_Loan.Book_id INNER JOIN Library_Branch
on Book_Loan.Branch_id =Library_Branch.Branch_id INNER JOIN Borrower
on Book_Loan.Card_no = Borrower.Card_no
where Library_Branch.Branch_name = ‘URDU Branch’ and Book_Loan.Due_date = ’21-5-13′

select Branch_name,COUNT (Book_id)
from Library_Branch INNER JOIN Book_Loan
on Library_Branch.Branch_id = Book_Loan.Branch_id
group by Library_Branch.Branch_name

select Title,No_Of_Copies
from BOOKS B INNER JOIN Book_Author
on B.Book_id = Book_Author .Book_id INNER JOIN Book_Copies
on B.Book_id =Book_Copies.Book_id INNER JOIN Library_Branch
on Book_Copies.Branch_id = Library_Branch.Branch_id
where Book_Author.Author_name = ‘Stephen King’ and Library_Branch.Branch_name = ‘RAIWAND Branch’

select name from borrower inner join book_loan on borrower.card_no=book_loan.card_no
where book_loan.status=’pending’

select name, Address, count(book_id) from borrower inner join book_loan
on book_loan .card_no=borrower.card_no where status=’pending’ group by borrower.name, borrower.Address
having COUNT(book_loan .book_id)>2

ayesha sulaiman bcs 4 query implementation (COMPANY DATABASE QUERY)

create table EMPLOYE
(
Fname varchar(50),
Minit varchar(50),
Lname varchar(50),
Ssn int unique,
Bdate varchar(50),
Adress varchar(50),
Sex varchar(50),
Salary int,
Super_ssn int,
Dno int
);

insert into EMPLOYE values (‘John’,'B’,'Smith’,123456789,’1965-01-09′,’731 Fondren,Houston, TX’,'M’,30000,333445555,5)
insert into EMPLOYE values (‘Franklin’,'T’,'Wong’,333445555,’1965-12-08′,’638 Voss,Houston, TX’,'M’,40000,888665555,5)
insert into EMPLOYE values (‘Alicia’,'J’,'Zelaya’,999887777,’1968-01-19′,’3321 Castle,Sprint, TX’,'F’,25000,987654321,4)
insert into EMPLOYE values (‘Jennifer’,'S’,'Wallace’,987654321,’1941-06-20′,’291 Berry,Bellaire, TX’,'F’,43000,888665555,4)
insert into EMPLOYE values (‘Ramesh’,'K’,'Narayan’,666884444,’1962-09-15′,’975 Fire Oak, Humble, TX’,'M’,38000,333445555,5)
insert into EMPLOYE values (‘Joyce’,'A’,'English’,453453453,’1972-07-31′,’5631 Rice, Houston, TX’,'M’,25000,333445555,5)
insert into EMPLOYE values (‘Ahmad’,'V’,'Jabbar’,987987987,’1969-03-29′,’980 Dallas, Houston, TX’,'M’,25000,987654321,4)
insert into EMPLOYE values (‘James’,'E’,'Borg’,888665555,’1937-11-10′,’450 Stone, Houston, TX’,'M’,55000,NULL,1)

select * from EMPLOYE

create table DEP
(
Dname varchar(50),
Dnumber int UNIQUE,
Mgr_ssn int,
Mgr_start_date varchar(50),
);

insert into DEP values (‘Research’,5,333445555,’1988-05-22′)
insert into DEP values (‘Administration’,4,987654321,’1995-01-01′)
insert into DEP values (‘Headquarters’,1,888665555,’1981-06-19′)

select *from DEP

create table WORKS_ON
(
Essn int,
Pno int,
Hours int,
);

insert into WORKS_ON values (123456789,1,32.5)
insert into WORKS_ON values (123456789,2,7.5)
insert into WORKS_ON values (666884444,3,40.0)
insert into WORKS_ON values (453453453,1,20.0)
insert into WORKS_ON values (453453453,2,20.0)
insert into WORKS_ON values (333445555,2,10.0)
insert into WORKS_ON values (333445555,3,10.0)
insert into WORKS_ON values (333445555,10,10.0)
insert into WORKS_ON values (333445555,20,10.0)
insert into WORKS_ON values (999887777,30,30.0)
insert into WORKS_ON values (999887777,10,10.0)
insert into WORKS_ON values (987987987,10,35.0)
insert into WORKS_ON values (987987987,30,5.0)
insert into WORKS_ON values (987654321,30,20.0)
insert into WORKS_ON values (987654321,20,15.0)
insert into WORKS_ON values (888665555,20,NULL)

select *from WORKS_ON

create table DEPT_LOCATION
(
Dnumber int ,
Dlocation varchar(50),
);

insert into DEPT_LOCATION values (1,’Houston’)
insert into DEPT_LOCATION values (4,’Stafford’)
insert into DEPT_LOCATION values (5,’Bellaire’)
insert into DEPT_LOCATION values (5,’Sugarland’)
insert into DEPT_LOCATION values (5,’Houston’)

select *from DEPT_LOCATION

create table PROJECT
(
Pname varchar(50),
Pnumber int UNIQUE,
Plocation varchar(50),
Dnum int,
);

insert into PROJECT values (‘ProductX’,1,’Bellaire’,5)
insert into PROJECT values (‘ProductY’,2,’Sugarland’,5)
insert into PROJECT values (‘ProductZ’,3,’Houston’,5)
insert into PROJECT values (‘Computerization’,10,’Stafford’,4)
insert into PROJECT values (‘Reorganization’,20,’Houston’,1)
insert into PROJECT values (‘Newbenefits’,30,’Stafford’,4)

select *from PROJECT

create table DEPENDENT
(
Essn int,
Dependent_name varchar(50),
Sex varchar(50),
Bdate varchar(50),
Relationship varchar(50),
);

insert into DEPENDENT values (333445555,’Alice’,'F’,’1986-04-05′,’Daughter’)
insert into DEPENDENT values (333445555,’Theodore’,'M’,’1983-10-25′,’Son’)
insert into DEPENDENT values (333445555,’Joy’,'F’,’1958-05-03′,’Spouse’)
insert into DEPENDENT values (987654321,’Abner’,'M’,’1942-02-28′,’Spouse’)
insert into DEPENDENT values (123456789,’Micheal’,'M’,’1988-01-04′,’Son’)
insert into DEPENDENT values (123456789,’Alice’,'F’,’1988-12-30′,’Daughter’)
insert into DEPENDENT values (123456789,’Elizabeth’,'F’,’1967-05-05′,’Spouse’)

select *from DEPENDENT

SELECT fname,SUM(Hours)
FROM EMPLOYE inner join WORKS_ON
ON EMPLOYE.Ssn=WORKS_ON.Essn
INNER JOIN PROJECT ON WORKS_ON.Pno=PROJECT.Pnumber
WHERE Dno=5 AND Pname=’PRODUCT X’
GROUP BY Fname
HAVING SUM(Hours) >10

SELECT fname FROM EMPLOYE INNER JOIN DEPENDENT
ON EMPLOYE.Ssn=DEPENDENT.Essn

SELECT PNAME,SUM(Hours)
FROM PROJECT INNER JOIN WORKS_ON
ON PROJECT.Pnumber=WORKS_ON.Pno
GROUP BY Pname

SELECT FNAME,LNAME FROM EMPLOYE INNER JOIN PROJECT
ON
EMPLOYE.Ssn=PROJECT.Pnumber

SELECT FNAME,LNAME FROM EMPLOYE
WHERE Ssn NOT IN (select ssn from works_on)

SELECT Dname, AVG(SALARY)
FROM DEPARTMENT INNER JOIN EMPLOYE
ON
DEPARTMENT.DCODE=EMPLOYE.Dno
GROUP BY DNAME

SELECT AVG (SALARY)
FROM EMPLOYE
WHERE Sex=’F’

SELECT FNAME,LNAME, Adress, COUNT (Pnumber)
FROM EMPLOYE INNER JOIN DEP
ON
EMPLOYE.Dno=DEP.Dnumber INNER JOIN PROJECT
ON
DEP.Dnumber=PROJECT.Dnum INNER JOIN DEPT_LOCATION
ON
PROJECT.Pnumber=DEPT_LOCATION.Dnumber
WHERE Plocation=’houston’ AND Dlocation ‘houston’
GROUP BY FNAME,LNAME, Adress

select fname,lname
from employe inner join DEP
ON EMPLOYE.Ssn=DEP.Mgr_ssn
where eMPLOYE.ssn not in (select ssn from dependent)

Login Page in ASP.net

To make a login page which would acess the database and tell the user that he has been loged on we will require a database table in which username and password is stored. This work will be done in 3 parts
First make a connection string Secondly accessing the data and thirdly loging in.

First we have to make a connection string the connection string is made in Web.Config like this

<connectionStrings>

<add name=”ConnectDBString” connectionString=”server=localhost;Database=test;Integrated Security=SSPI;” />

</connectionStrings>

by this we have made a connection string a connection string has 3 basic parts 1. Server Name 2. Database Name and 3. Authentication Type.
After making the connection string we have to get data from the database to get the data we will use connection less approach and we will get data by calling a stored procedure from database
To get data from database we write these lines to read the connection string from web.config

public db_data()
{
myConnectionString = ConfigurationManager.ConnectionStrings["ConnectDBString"].ConnectionString;

try
{
objcon = new SqlConnection(myConnectionString);
}

catch (Exception ex)
{

}
}

public DataTable Athuntication(string username, string password)
{
SqlCommand m_SqlCmd = new SqlCommand();
m_SqlCmd.CommandText = “dbo.[pr_select_login]“;
m_SqlCmd.CommandType = CommandType.StoredProcedure;
DataTable dtToReturn = new DataTable(“tbl_login”);
SqlDataAdapter m_SqlAdp = new SqlDataAdapter(m_SqlCmd);
m_SqlCmd.Connection = objcon;
try
{
m_SqlCmd.Parameters.Add(new SqlParameter(“@username”, SqlDbType.VarChar, 30, ParameterDirection.Input, false, 10, 0, “”, DataRowVersion.Proposed, username));
m_SqlCmd.Parameters.Add(new SqlParameter(“@password”, SqlDbType.VarChar, 50, ParameterDirection.Input, false, 10, 0, “”, DataRowVersion.Proposed, password));

m_SqlAdp.Fill(dtToReturn);
return dtToReturn;
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
return null;
}

}

}

Now we have a datatable full of required data we can use this for logging in.