assignment no 5 solution of CS403

Subject: assignment no 5 solution of CS403 Tue Jul 20, 2010 9:03 pm

Quote :: Question No.1
Solution:

1:CREATE TABLE
MEMBER ( Memberid_varchar (10) NOT NULL, First name _varchar (20),
Lastname_varchar (20), Date of birth_Date (15), City_varchar (15), Zip
code Email_varchar (30), and Date of joining-Date (15));

2: CREATE
TABLE ATTENDANCE (Meeting date_Date (15), Location_varchar
(30), Member Attended 'CHAR (20)', Member.id_varchar (10) NOT NULL
References MEMBER);

3: CREATE TABLE DOCUMENTARY
(Documentarid_varchar (10) NOT NULL, Documentary name_varchar (15), Year
Released Date (15), Available on DVD_varchar (10), Rating No (10), and
Categoryid_varcar (10), NOT NULL References MEMBER);

4: CREATE
TABLE CATEGORY (Categorid_varchar (10), NOT NULL,
Category_varchar (10));

5: CREATE TABLE FAVOURITE CATEGORY
(Categoryid_varchar (10) NOT NULL References CATEORY, Memberid_varchar
(10) References MEMBER);
Step 2:
What to do
next???
After creating these tables successfully you are required to
enter Five (5) records in each of the tables using SQL’s
INSERT command.
Solution:

INSERT INTO
MEMBER VALUES(654,’Saba’,’irum’,15-8-1985,Toba,Saba@gmail.com,15-7-1989)
INSERT INTO
MEMBER VALUES(655,’Saba’,’irum’,15-8-1985,Toba,Saba@gmail.com,15-7-1989)
INSERT INTO
MEMBER VALUES(656,’Saba’,’irum’,15-8-1985,Toba,Saba@gmail.com,15-7-1989)
INSERT INTO
MEMBER VALUES(657,’Saba’,’irum’,15-8-1985,Toba,Saba@gmail.com,15-7-1989)
INSERT INTO
MEMBER VALUES(658,’Saba’,’irum’,15-8-1985,Toba,Saba@gmail.com,15-7-1989)

INSERT INTO
ATTENDANCE VALUES(12-5-2010,’FSD’,’yes’,654)
INSERT INTO
ATTENDANCE VALUES(12-5-2010,’FSD’,’yes’,655)
INSERT INTO
ATTENDANCE VALUES(12-5-2010,’FSD’,’yes’,656)
INSERT INTO
ATTENDANCE VALUES(12-5-2010,’FSD’,’yes’,657)
INSERT INTO
ATTENDANCE VALUES(12-5-2010,’FSD’,’yes’,658)

INSERT INTO
DOCUMENTARY VALUES(45’,Member’,1999,’yes’,15000,95)
INSERT INTO
DOCUMENTARY VALUES(46,’Member’,1999,’yes’,15000,96)
INSERT INTO
DOCUMENTARY VALUES(47,’Member’,1999,’yes’,15000,97)
INSERT INTO
DOCUMENTARY VALUES(48,’Member’,1999,’yes’,15000,98)
INSERT INTO
DOCUMENTARY VALUES(49,’Member’,1999,’yes’,15000,99)

INSERT INTO
CATEGORY VALUES(95,’People’)
INSERT INTO CATEGORY
VALUES(96,’People’)
INSERT INTO CATEGORY VALUES(97,’People’)
INSERT INTO
CATEGORY VALUES(98,’People’)
INSERT INTO CATEGORY
VALUES(99,’People’)

INSERT INTO FAVOURITE CATEGORY
VALUES(95,654)
INSERT INTO FAVOURITE CATEGORY VALUES(96,655)
INSERT INTO
FAVOURITE CATEGORY VALUES(97,656)
INSERT INTO FAVOURITE
CATEGORY VALUES(98,657)
INSERT INTO FAVOURITE CATEGORY
VALUES(99,658)

Q2. Differentiate ALTER and UPDATE
table commands in SQL. You are required to write example SQL queries for
illustration.
Answer:
The purpose
of Alter statement is to make changes in the definition of a table. It
can add or drops columns in an existing table.

SQL ALTER TABLE Syntax
1). To add a
column in a table, the following syntax:
ALTER TABLE
table_name
ADD column_name datatype
2). To delete a
column in a table, the following syntax
ALTER TABLE
table_name
DROP COLUMN column_name
3). To change
the data type of a column in a table,the following syntax:
ALTER TABLE
table_name
ALTER COLUMN column_name datatype
Query:-
ALTER TABLE
ATTENDENCE
DROP COLUMN MemberAttended
The UPDATE
Statement:-
The UPDATE statement is used to update
existing records in a
table.
SQL UPDATE
Syntax:-
UPDATE table_name
SET column1=value, column2=value2...
WHERE
some_column=some_value
Query:-
UPDATE
ATTENDENCE
SET Location ='Karachi'
WHERE
Location='FSD'
Tags:

» CS403 ASSIGNMENT # 3 SOLUTION
» assignment no 5 solution of cs4-1
» CS402 3RD ASSIGNMENT SOLUTION
» MGT111 ASSIGNMENT NO 1 SOLUTION
» mgmt627 assignment no 2 solution