SQL Practice for Naïve Users…These queries would be written in Query Analyser
Create table commands
CREATE TABLE Member
(
MemberId integer primary key NOT NULL,
FirstName nvarchar(50),
LastName nvarchar(50),
DateOfBirth datetime,
City varchar(75),
ZipCode varchar(12),
Email varchar(200),
DateOfJoining datetime
);
CREATE TABLE Attendance
(
MeetingDate datetime,
Location varchar(200),
MemberAttended char(1),
MemberId integer foreign key references Member(MemberId),
);
CREATE TABLE Documentary
(
DocumentaryId integer Primary key NOT NULL,
DocumentaryName varchar(100),
YearReleased integer,
AvailableOnDVD char(1),
Rating varchar(5),
CategoryId integer
);
CREATE TABLE Category
(
CategoryId integer Primary Key NOT NULL,
Category varchar(100)
);
CREATE TABLE FavouriteCategory
(
CategoryId integer foreign key references Category(CategoryId),
MemberId integer foreign key references Member(MemberId)
);
1
Virtual University of Pakistan
SQL Practice for Naïve Users…These queries would be written in Query Analyser
Figure 1. Member Table
2
Virtual University of Pakistan
SQL Practice for Naïve Users…These queries would be written in Query Analyser
Figure 2. Attendance Table
3
Virtual University of Pakistan
SQL Practice for Naïve Users…These queries would be written in Query Analyser
Figure 3. Documentary Table
4
Virtual University of Pakistan
SQL Practice for Naïve Users…These queries would be written in Query Analyser
Figure 4. CategoryTable
5
Virtual University of Pakistan
SQL Practice for Naïve Users…These queries would be written in Query Analyser
Figure 5. FavouriteCategory Table
Question No. 2
The ALTER TABLE command modifies the schema of the table. DBMS
like SQL Server allows addition, removal and modification of any columns in an existing
table.
Example SQL Queries:
Alter table Member
ADD Address Varchar(30);
6
Virtual University of Pakistan
SQL Practice for Naïve Users…These queries would be written in Query Analyser
Figure 6. The Use of ALTER Table Command
ALTER table Member
Drop Column Address; // Will delete Address column from Member table.
On the other hand UPDATE TABLE command is used for setting or changing values in
a data table.
Example SQL Queries:
UPDATE Member
SET City = ‘LHR’ WHERE Empid = ‘30’;
7
Virtual University of Pakistan
SQL Practice for Naïve Users…These queries would be written in Query Analyser
Figure 7. The Use of Update Table Command
The End!!!
8
Virtual University of Pakistan