Introduction
The Microsoft SQL Server 2008 is a complete database management system. You can download the SQL Server 2008 express edition free of charge from http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx .However SQL Server express is strictly a database server, or database engine. ie. it doesn't provide some of the client tools.
How to start database server
The database server will start automatically each time you start your computer by default.
To stop the server run services and click stop on the right pane( To run services click start --> Run --> and type Services.msc)
How to enable remote connections
Remote connections are disabled by default. To enable remote connections use the Surface Area Configuration tool.
To display Surface Area Configuration select Start--> All Programs --> Microsoft SQL Server 2008 --> Configuration Tools --> SQL Server Surface Area Configuration.
To enable remote connections select Surface Area Configuration for services and connections --> Database Engine --> Local and Remote Connections.
How to open and save queries
To open a new query window, click the New Query that appears on top left hand side of the window. To save the query press ctrl+s.
To create a database use the CREATE DATABASE statement as below.
Syntax:
CREATE DATABASE {database name}
Example:
CREATE DATABASE Videodb
Once database is created, you can use the the database to create various types of objects like tables by executing the following command.
USE {database name}
example:
USE Videodb
How to create a Table
Syntax:
Example:
CREATE TABLE ItemDetails(ItemNo INT NOT NULL IDENTITY PRIMARY KEY,
Type VARCHAR(10) ,
Artist VARCHAR(20),
DatePurchased DATETIME,
Cost MONEY)
Example explained
- INT Represents integer data type
- NOT NULL This column cannot have any null values
- IDENTITY values will be auto generated. IDENTITY(1,10) will start numbering from 1 and increment the number by 10.
- PRIMARY KEY A unique identifier
- DATETIME is a datatype used to insert a date value. (SMALLDATETIME is also can be used)
- MONEY is a datatype used to insert a currency (SMALLMONEY is also can be used)
Following statement will add a new column to the above table
ALTER TABLE ItemDetails
ADD Title VARCHAR(20)
- Following statement will delete a column
DROP COLUMN Cost
How to Insert data in to Table
Syntax:
INSERT INTO {table name [column name 1 ,column name 2,...]} VALUES({value 1, value2,...})
Example:
INSERT INTO ItemDetails VALUES('DVD','Clarens','10/10/2010',500)
Example Explained
- A value for the InvoiceID is not inserted as it is an IDENTITY
- Date value should be single quated and the format is DD/MM/YYYY
- Retrive data from a single table
Example:
SELECT ItemNo,Title FROM ItemDetails
- Retrieving data from two or more tables
This comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by a blog administrator.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteQuestion-06*****
ReplyDelete1)SELECT *
FROM patient_master
WHERE do_registration between (2010/10/10)
AND (2010/10/12);
*****this above one shows only the column names*****
sir..,can you plz explain how to do this 1st one....????
2)update patient_master set name='ram' where patient_id='pt011';
3)select results,patient_name from labtest_info where test_date = '20-07-08';
4)select count(patient_no) as patients from patient_master where doc_code='abc';
5)select max(doc_code)as doctor from patient_master where patient_no like 'p%';
6)update bed_info set bed_no ='456' where patient_no='pt023';
7)update bed_info set status='not allotted' where bed_no='123';
8)select bed_no,status from bed_info where ward_no='10';
9)select patient_name from patient_master where doc_code='doc1300' and gender='male';
10)select * from patient_master where age > 50 and doc_name like 'das%';
This comment has been removed by the author.
ReplyDeleteQuestion-01
ReplyDelete1) select cl_name,city from client_details;
2) select pro_name,product_no,description from product;
3) select cl_name from client_details where cl_name like '%[a]%';
4) select cl_name from client_details where city='colombo';
5) select pro_name,product_no from product_master where selli_price >=5000;
6) ALTER TABLE product_master
ADD new_price money;
7) sp_rename 'sales_order_details.product_rate', 'new_product_rate', 'COLUMN';
8) select pro_name,product_no from product order by description;
9) select orderno,orderdate from sales_order_details;
10) delete from sales_order_details where deliverdate<'2010-08-25';
11) update sales_order_details set deliverdate='16-08-2010' where orderno='on01008';
12) update client_details set tot_balance='1200' where client_no='cn1003';
13) select product_no,pro_name from product where description like'hdd1034''dvdrw';
14) select cl_name,city,state from client_details where city !='kandy';
15) select orderno from sales_order_details where canceled_date='%-03-2010%';
Q1)
ReplyDeletecreate database salesInfo
use salesInfo
create table client(cno varchar(3) primary key not null,cname varchar(30) not null,addr1 varchar(15),
city varchar(15),pincode numeric(6),stat varchar(10),balance numeric(7,2))
create table salesman(sno varchar(3) primary key not null,sname varchar(30) not null,addr1 varchar(15),city varchar(15),
pincode numeric(6),stat varchar(10),sal numeric(7,2))
create table product(pno varchar(3) primary key not null,pname varchar(30) not null,price numeric(7,2),manufacture varchar(30))
create table orders(OrderNo varchar(3) primary key not null,oreder_date datetime,cno varchar(3),sno varchar(3) unique,
foreign key(cno) references client(cno),foreign key(sno) references salesman(sno),delivery_date datetime,
qty_ordered numeric(3),rate numeric(3))
a)select cname,city from client
b)select pno,pname from product
c)select cname from client where cname like '_a%'
d)select cname from client where city='Colombo'
e)select pno,pname,price from product where price>2000 and price<=5000
f)alter table product add NEW_PRICE numeric(7,2)
g)alter table salesman rename rate new_product_rate numeric(3)
h)sp_rename 'orders.rate','new_product_rate','column';
i)select pname from product order by pname
j)delete from orders where delivery_date<'8/25/2010'
k)update orders set delivery_date='8/16/2010' where orderno='O02'
l)update client set balance=1200 where cno='C01'
m)select*from product where pname like '%HDD1034%' and pname like '%DVDRW%'
n)select cname,city,stat from client where city<>'kandy'
Q1
ReplyDeletea)Select client_name ,city from Client
b) Select * from Product
c) Select name from Clients where name like ‘%a’
d)select client_name from client where city='colombo';
e) Select * from Product where selling_price <2000 and >=5000
f) ALTER TABLE Product ADD NEW_PRICE money
g)ALTER TABLE Sales_Order_Details RENAME product_rate to new_product_rate.
h) Select * from Products ORDERBY description
i) Select cno ,sales_order_date from Sales_Order_Details
j) Delete from Salesmen where del_date >25/8/2010
k) update Salesmen SET del_date 16-08-10 where sales_order_date=’ ON01008’
l) update client SET bal_due=1200 where cno=’ CN01003’
m) select * from product where description=’ HDD1034’ and description= ‘DVDRW’
n)select client_name,city,state from client where city !='kandy';
o)select order_no from Sales_Order_Details where canceled_date='%-03-2010%';
Q4)
a)Select * from customer where account_type=’savings’
b)Select name,address from customers where loan_amount >50,000
c)Update customers set name=’ABC’ where account_number=’ ’TU001’
d)Select account_number from customer_transaction where tot_deposit>80,000
e)Select count (*) from customers where account_type= ’fixed deposit’
f)Select * from customers where date_created between ’20-01-08’ to ’20-09-08’
g)Select * from customer_transaction where date=28/08/ 2008.
h)Select total(deposit) “Total Deposit” and total(withdrawn) “Total withdrawn” from customer_transaction where date=29/08/2008
i)Select * from customers where loan=’yes’
Q6)
a)Select * from patients where date_of_admission between ‘20/7/2002’ and ’20/09/2008’
b)Update patients set pname=’Ram’ where pid=’PT011’
c)Select pname, lab_test_results from patients where date=20/07/2008’.
d)Select count (*) from patients where doctorname=’ABC’
e)select max(doctor_num) 'doctor' from patient where pid like 'p%'
f)Update patient set bed_number=456 where pid=’PT023’
g)Update bed set status=’not allotted’ where bed_num=123
h)Select * from bed where ward_num=10 and status=’not allotted’
i)Select * from patients where ward_num=13 and doctor_name=’XYZ’
j)Select * from patients where age>50 and doctor_name like ‘%das’
QUESTION-04
ReplyDelete1)select * from customers where acc_type='savings';
2)select cus_name,cus_address from cutomers where loan_amount > 50000;
3)update customers set cus_name='abc' where acc_no='tu001';
4)select acc_no from transactions amount >'80000';
5)select acc_no from accounts where acc_type='fixed deposits';
6)select cus_id,cus_name,address,age,contact_no from customers where date_created between '20-01-08'and'20-08-08';
7)select * from transactions where trans_date='20-08-08';
8)select sum(amount) as tot_amount from transactions where trans_date='29-08-08';
9)select * from customers where loan_status='yes';
QUESTION-05
ReplyDelete1)select title from books where date_issued between '21-08-08' and '29-08-08';
2)select count(book_id)as books from books where author like 'murach';
3)select max(publisher) from journals;
4)select count(title)as books from books;
5)select count(book_issued) as issuedbooks from journals where student_id='csb06001';
6)update books set author='kamals' where book_id='bk003' and title='abc';
7)select student_id from journals title='database systems' and author='E.navathe';
8)select count(book_issued) as issued from journals;
9)select title from books where subject like 'ora';
Question-03
ReplyDelete1)select * from emp_master where department='human resource';
2)select * from pay_data where gross_salary >='21000';
3)select dob,date_of_hire from emp_master where emp_name='dilshan';
4)select leaves_taken,emp_name from pay_data;
5)select department,emp_name from emp_master;
6)select grades,emp_name from emp_master where department='mis';
7)select sum(gross_salary*tax_rate/100) as tax from pay_data where emp_name='kamal';
8)select emp_no,emp_name from emp_master where date_joined between '2000-07-23' and '2005-07-20';
9)select mail_address from emp_master where department='human resource';
10)select (availale_leaves-leaves_taken)as total_leave_available
from emp_master join pay_data
on emp_master.emp_name=pay_data.emp_name;
11)alter table emp_master
add emp_phone int
select * from emp_master;
12)select emp_no,emp_name from pay_data where basic_salary='20000';
13)?????????????????????????????
14)select emp_name from pay_data where bank_loan_status='yes';
15)select (gross_salary-basic_salary) as bank_balance from pay_data where emp_no='34536';
how much money does Freelance pay you for one click on a ad??
ReplyDeleteQ1
ReplyDeletea) SELECT client_name,city FROM client
b) SELECT * FROM product
c) SELECT client_name FROM client WHERE client_name LIKE '_a%'
d) SELECT * FROM client WHERE city='Colombo'
e) SELECT * FROM product WHERE selling_price>2000 and <=5000
f) ALTER TABLE product_master ADD new_price
g) ALTER TABLE Sales_Order_Details RENAME product_rate TO new_product_rate
h) SELECT * FROM product SORT BY product_name
i) SELECT order_number,date FROM order
j) DELETE FROM order WHERE delivery_date IS BEFORE 25/08/2010
k) UPDATE order SET delivery_date='16-08-10' WHERE order_number='ON01008'
l) UPDATE client SET bal_due=1200 WHERE client_no=CN01003
m) SELECT * FROM product WHERE product_name LIKE 'HDD1034-DVDRW'
n) SELECT client_name,city,state FROM client where city NOT 'Kandy'
o) SELECT * FROM order WHERE sales_order_date='MARCH'