�ݺ�ߣ

//Downloaded From www.c4cpp.co.nr
//(C)2009.All rights reserved.
STRUCTURED QUERY LANGUAGE-
1
TABLE: SPORTS
Student
No:
class Name Game 1 Grade 1 Game 2 Grade 2
10 7 Sammer Cricket B Swimming A
11 8 Sujith Tennis A Skating C
12 7 Kamal Swimming B Football B
13 7 Venna Tennis C Tennis A
14 9 Archana Basketball A Cricket A
15 10 Arpit Cricket A Athletics C

WRITE THE SQL COMMANDS FOR (1) TO (7) ON THE BASIS OF
THE TABLE SPORTS
1) Display the names of students who have grade ‘C ’in either Game 1
or Game 2 or both.
SELECT NAME
FROM SPORTS
WHERE GRADE 1 =’C’. OR. GRADE 2 = ‘C’;
OUTPUT
NAME
SUJITH
VENNA
ARPIT
2)Display the number of students getting grade ‘A’ in cricket.
SELECT COUNT (*) FROM SPORTS
WHERE GRADE1 =”A” .AND. GAME2=”CRICKET” .OR. GRADE2 =”A”
.AND. GAME2=”CRICKET”;
OUTPUT
COUNT(*)
2
3)Display the names of the students who have same game for both Game 1 and
Game 2.
SELECT NAME FROM SPORTS
WHERE GAME 1 =GAME 2;
OUTPUT
NAME
VENNA

4)Display the games taken up by the students ,whose name starts with ‘A’.
SELECT GAME1,GAME2
FROM SPORTS
WHERE NAME LIKE (”A%”);
OUTPUT
GAME1 GAME2
BASKETBALL CRICKET
CRICKET ATHELETICS
5) Add a new column named ‘Marks’.
ALTER TABLE SPORTS
ADD MARKS FLOAT (6, 2);
OUTPUT
ONE COLUMN ADDED
6)Assign a value 200 for Marks for all those who are getting grade ‘B’ or grade
‘A’ in both Game1 and Game2.
UPDATE SPORTS SET MARKS =200
WHERE GRADE1<=”B” AND. GRADE2<=”B”;
OUTPUT
3 RECORDS UPDATED
7)Arrange the whole table in the alphabetical order by name.
SELECT*FROM SPORTS
ORDER BY NAME;

OUTPUT
Student
No:
class Name Game 1 Grade 1 Game 2 Grade 2
14 9 Archana Basketball A Cricket A
15 10 Arpith Cricket A Athletics C
12 7 Kamal Swimming B Football B
10 7 Sammer Cricket B Swimming A
11 8 Sujith Tennis A Skating C
13 7 Venna Tennis C Tennis A

STRUCTURED QUERY LANGUAGE
-2
TABLE:STUDENT
No: Name Stipend Stream Avgmark Grade Class
1 Karan 400.00 Medical 78.5 B 12B
2 Divakar 450.00 Commerce 89.2 A 11C
3 Divya 300.00 Commerce 68.6 C 12C
4 Arun 350.00 Humanities 73.1 B 12C
5 Sabina 500.00 Nonmedical 90.6 A 11A
6 John 400.00 Medical 75.4 B 12B
7 Robert 250.00 Humanities 64.4 C 11A
8 Rubina 450.00 Nonmedical 88.5 A 12A
9 Vikas 500.00 Nonmedical 92.0 A 12A
10 Mohan 300.00 Commerce 67.5 C 12C

WRITE SQL COMMANDS FOR QUESTIONS 1 TO 7 ON THE BASIS OF TABLE
STUDENT
1)Select all the Nonmedical stream students from STUDENT.
SELECT NAME
FROM STUDENT
WHERE STREAM=’NONMEDICAL’;
OUTPUT
NAME:
SABINA
RUBINA
VIKAS
2)list the names of those students who are in class 12 sorted by stipend
SELECT NAME,STIPEND FROM STUDENT
WHERE CLASS=’12’
ORDER BY STIPEND;
OUTPUT
NAME STIPEND
DIVYA 300.00
MOHAN 300.00
ARUN 350.00
KARAN 400.00
JOHN 400.00
RUBINA 450.00
VIKAS 500.00
3)list all students sorted by avgmark in descending order
SELECT NAME FROM STUDENT
ORDER BY AVGMARK DESC;
OUTPUT
ROBERT

MOHAN
DIVYA
ARUN
JOHN
KARAN
RUBINA
DIVAKAR
SABINA
VIKAS
4)To count the number of students with grade “A”
SELECT COUNT(GRADE)
FROM STUDENT
WHERE GRADE=”A”;
OUTPUT
4
5)to insert a new student in the table anf fill the columns with some values
INSERT INTO STUDENT VALUES
(11,”MANISHA”,300.00,”NONMEDICAL”,72.5,”B”,12C);
OUTPUT
ONE ROW ADDED
6)give the output of the following SQL statement:
A)SELECT MIN(AVGMARK)
FROM STUDENT
WHERE AVGMARK<75;
OUTPUT
64.4
B)SELECT SUM(STIPEND) FROM STUDENT WHERE GRADE=”B”
OUTPUT
1150.00
C)SELECT AVG(STIPEND)FROM STUDENT WHERE CLASS=”12A”;
OUTPUT

475.00
D)SELECT COUNT(DISTINCT)
OUTPUT
10

�ݺ�ߣ

solDocument

More Related Content

solDocument