Simple SQL Query

 

Question 10

Write SQL commands for the following on the basis of given table STUDENT :

Table : STUDENT

StudentNo.ClassNameGAMEGrade1SUPWGrade2
107SameerCricketBPhotographyA
118SujitTennisAGardeningC
127KamalSwimmingBPhotographyB
137VeenaTennisCCookingA
149ArchanaBasket BallALiteratureA
1510ArpitCricketAGardeningC

  1. Display the names of the students who are getting a grade 'C' in either GAME or SUPW.
  2. Display the different games offered in the school.
  3. Display the SUPW taken up by the students, whose name starts with 'A'
Code to create Table :

CREATE TABLE StudentActivities (
    StudentNo INT PRIMARY KEY,
    Class INT,
    Name VARCHAR(50),
    Game VARCHAR(30),
    Grade1 CHAR(1),
    SUPW VARCHAR(50),
    Grade2 CHAR(1
Code to enter values: 
NSERT INTO StudentActivities (StudentNo, Class, Name, Game, Grade1, SUPW, Grade2) VALUES
(10, 7, 'Sameer', 'Cricket', 'B', 'Photography', 'A'),
(11, 8, 'Sujit', 'Tennis', 'A', 'Gardening', 'C'),
(12, 7, 'Kamal', 'Swimming', 'B', 'Photography', 'B'),
(13, 7, 'Veena', 'Tennis', 'C', 'Cooking', 'A'),
(14, 9, 'Archana', 'Basket Ball', 'A', 'Literature', 'A'),
(15, 10, 'Arpit', 'Cricket', 'A', 'Gardening', 'C');

Question 11

Write SQL commands for the following on the basis of given table SPORTS :

Table : SPORTS

StudentNo.ClassNameGame1Grade1Game2Grade2
107SameerCricketBSwimmingA
118SujitTennisASkatingC
127KamalSwimmingBFootballB
137VennaTennisCTennisA
149ArchanaBasketballACricketA
1510ArpitCricketAAthleticsC
  1. Display the names of the students who have grade 'C' in either Game1 or Game2 or both.
  2. Display the names of the students who have same game for both Game1 and Game2.
  3. Display the games taken up by the students, whose name starts with 'A'.
Code to Create a Table : 
CREATE TABLE SPORTS (
    StudentNo INT PRIMARY KEY,
    Class INT,
    Name VARCHAR(50),
    Game1 VARCHAR(30),
    Grade1 CHAR(1),
    Game2 VARCHAR(30),
    Grade2 CHAR(1)

Code to Enter values: 
NSERT INTO SPORTS (StudentNo, Class, Name, Game1, Grade1, Game2, Grade2) VALUES
(10, 7, 'Sameer', 'Cricket', 'B', 'Swimming', 'A'),
(11, 8, 'Sujit', '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');
Question 12


Write SQL commands for the following on the basis of given table CLUB :

Table : CLUB

COACH_IDCOACHNAMEAGESPORTSPAYSEXDATOFAPP
1KUKREJA35KARATE1000M1996-03-27
2RAVINA34KARATE1200F1998-01-20
3KARAN34SQUASH2000M1998-02-19
4TARUN33BASKETBALL1500M1998-01-01
5ZUBIN36SWIMMING750M1998-01-12
6KETAKI36SWIMMING800F1998-02-24
7ANKITA39SQUASH2200F1998-02-20
8ZAREEN37KARATE1100F1998-02-22
9KUSH41SWIMMING900M1998-01-13
10SHAILYA37BASKETBALL1700M1998-02-19
  1. To show all information about the swimming coaches in the club.
  2. To list names of all coaches with their date of appointment (DATOFAPP) in descending order.
  3. To display a report, showing coachname, pay, age and bonus (15% of pay) for all the coaches.
Code to create table:

REATE TABLE COACH (
    COACH_ID INT PRIMARY KEY,
    COACHNAME VARCHAR(50),
    AGE INT,
    SPORTS VARCHAR(30),
    PAY DECIMAL(10, 2),
    SEX CHAR(1),
    DATOFAPP DATE

Code to Enter Values: 
INSERT INTO COACH (COACH_ID, COACHNAME, AGE, SPORTS, PAY, SEX, DATOFAPP) VALUES
(1, 'KUKREJA', 35, 'KARATE', 1000, 'M', '1996-03-27'),
(2, 'RAVINA', 34, 'KARATE', 1200, 'F', '1998-01-20'),
(3, 'KARAN', 34, 'SQUASH', 2000, 'M', '1998-02-19'),
(4, 'TARUN', 33, 'BASKETBALL', 1500, 'M', '1998-01-01'),
(5, 'ZUBIN', 36, 'SWIMMING', 750, 'M', '1998-01-12'),
(6, 'KETAKI', 36, 'SWIMMING', 800, 'F', '1998-02-24'),
(7, 'ANKITA', 39, 'SQUASH', 2200, 'F', '1998-02-20'),
(8, 'ZAREEN', 37, 'KARATE', 1100, 'F', '1998-02-22'),
(9, 'KUSH', 41, 'SWIMMING', 900, 'M', '1998-01-13'),
(10, 'SHAILYA', 37, 'BASKETBALL', 1700, 'M', '1998-02-19');

Question 13

Write SQL commands for the following on the basis of given table STUDENT1 :

Table : STUDENT1

No.NameStipendStreamAvgMarkGradeClass
1Karan400.00Medical78.5B12B
2Divakar450.00Commerce89.2A11C
3Divya300.00Commerce68.6C12C
4Arun350.00Humanities73.1B12C
5Sabina500.00Nonmedical90.6A11A
6John400.00Medical75.4B12B
7Robert250.00Humanities64.4C11A
8Rubina450.00Nonmedical88.5A12A
9Vikas500.00Nonmedical92.0A12A
10Mohan300.00Commerce67.5C12C
  1. Select all the Nonmedical stream students from STUDENT1.
  2. List the names of those students who are in class 12 sorted by Stipend.
  3. List all students sorted by AvgMark in descending order.
  4. Display a report, listing Name, Stipend, Stream and amount of stipend received in a year assuming that the Stipend is paid every month.

Code to create table 

REATE TABLE StudentScholarship (
    No INT PRIMARY KEY,
    Name VARCHAR(50),
    Stipend DECIMAL(8, 2),
    Stream VARCHAR(30),
    AvgMark DECIMAL(5, 1),
    Grade CHAR(1),
    Class VARCHAR(10)

Code to enter Values:

INSERT INTO StudentScholarship (No, Name, Stipend, Stream, AvgMark, Grade, Class) VALUES
(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');

Question 16

Write SQL commands for the following on the basis of given table MOV :

Table : MOV

NoTitleTypeRatingStarsQtyPrice
1Gone with the WindDramaGGable439.95
2Friday the 13thHorrorRJason269.95
3Top GunDramaPGCruise749.95
4SplashComedyPG13Hanks329.95
5Independence DayDramaRTurner319.95
6Risky BusinessComedyRCruise244.95
7CocoonScifiPGAmeche231.95
8Crocodile DundeeComedyPG13Harris269.95
9101 DalmatiansComedyG359.95
10TootsieComedyPGHoffman129.95
  1. Display a list of all movies with Price over 20 and sorted by Price.
  2. Display all the movies sorted by QTY in decreasing order.
  3. Display a report listing a movie number, current value and replacement value for each movie in the above table. Calculate the replacement value for all movies as : QTY * Price * 1.15.
Code to create a table: 
REATE TABLE Movies (
    No INT PRIMARY KEY,
    Title VARCHAR(100),
    Type VARCHAR(20),
    Rating VARCHAR(10),
    Stars VARCHAR(50),
    Qty INT,
    Price DECIMAL(6, 2)

Code to insert Values:

INSERT INTO Movies (No, Title, Type, Rating, Stars, Qty, Price) VALUES
(1, 'Gone with the Wind', 'Drama', 'G', 'Gable', 4, 39.95),
(2, 'Friday the 13th', 'Horror', 'R', 'Jason', 2, 69.95),
(3, 'Top Gun', 'Drama', 'PG', 'Cruise', 7, 49.95),
(4, 'Splash', 'Comedy', 'PG13', 'Hanks', 3, 29.95),
(5, 'Independence Day', 'Drama', 'R', 'Turner', 3, 19.95),
(6, 'Risky Business', 'Comedy', 'R', 'Cruise', 2, 44.95),
(7, 'Cocoon', 'Scifi', 'PG', 'Ameche', 2, 31.95),
(8, 'Crocodile Dundee', 'Comedy', 'PG13', 'Harris', 2, 69.95),
(9, '101 Dalmatians', 'Comedy', 'G', NULL, 3, 59.95),
(10, 'Tootsie', 'Comedy', 'PG', 'Hoffman', 1, 29.95);

Question 17

Write SQL commands for the following on the basis of given table Teacher :

Table : Teacher

NoNameAgeDepartmentSalarySexDateofjoin
1Jugal34Computer12000M1997-01-10
2Sharmila31History20000F1998-03-24
3Sandeep32Maths30000M1996-12-12
4Sangeeta35History40000F1999-07-01
5Rakesh42Maths25000M1997-09-05
6Shyam50History30000M1998-06-27
7Shiv Om44Computer21000M1997-02-25
8Shalakha33Maths20000F1997-07-31
  1. To show all information about the teacher of history department.
  2. To list the names of female teachers who are in Hindi department.
  3. To list names of all teachers with their date of joining in ascending order.
Code to create the table:

CREATE TABLE Employee (
    No INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Department VARCHAR(30),
    Salary DECIMAL(10, 2),
    Sex CHAR(1),
    DateOfJoin DATE

Code to Enter the Values:

INSERT INTO Employee (No, Name, Age, Department, Salary, Sex, DateOfJoin) VALUES
(1, 'Jugal', 34, 'Computer', 12000, 'M', '1997-01-10'),
(2, 'Sharmila', 31, 'History', 20000, 'F', '1998-03-24'),
(3, 'Sandeep', 32, 'Maths', 30000, 'M', '1996-12-12'),
(4, 'Sangeeta', 35, 'History', 40000, 'F', '1999-07-01'),
(5, 'Rakesh', 42, 'Maths', 25000, 'M', '1997-09-05'),
(6, 'Shyam', 50, 'History', 30000, 'M', '1998-06-27'),
(7, 'Shiv Om', 44, 'Computer', 21000, 'M', '1997-02-25'),
(8, 'Shalakha', 33, 'Maths', 20000, 'F', '1997-07-31');

Comments

Post a Comment

Popular posts from this blog

The Road Not Taken

 The Road Not Taken  The Road Not Taken -       Robert Frost Introduction The poem ‘The Road Not Taken’ is based on common day to day life experience, when we have to make one choice out of many choices. At this point in time, we need to make the fullest use of our wisdom to choose the right option. In this poem, the two roads symbolize the choices that are presented by the life to us. Our future and the way of living our life totally depend on the decisions taken by us in the present time. Later on, sometimes, we look back and become thoughtful about those decisions we took in the past. Peeping in the past, we also wonder how our life would have been completely different had we chosen the other options. Title The title of the poem suggests us that it is indicating about absence. It is about the road that was “Not Taken” by the poet. And it gives him time and space to rethink about his decision. But on the other hand, the title may suggest that the poe...
Read more

Chapter 4 The Basic Writing Skills

  Chapter 4 Basic Writing Skills 1. Tick () the correct option. a. Which of the following field(s) use writing as a medium of communication? i. Medicine                                       ii    Education      iii. Technology                               iv All of these   b. Which of the following is necessary for effective writing? i Grammar and spellings                 ii. Concept and idea iii. Environment                      ...
Read more

The Fun They Had

Chapter 1 The Fun They Had Additional Questions: Q1. What are the reactions of Margie and Tommy to the real book? Q2. How were Tommy and Margie assessed in their subjects? Q3. Which is, according to you, an ideal school – the one where students study together in a special building or the room at home with a mechanical teacher (computer) in it? Q5. Read the following extract and answer the questions that follow. He was round little man with a red face and a whole box of tools with dials and wires. He smiled at Margie and gave her an apple, then took the  teacher  apart. Margie had hoped he wouldn’t know how to put it together again, but he knew how all right, and, after an hour or so, there it was again, large and black and ugly, with a big screen on which all the lessons were shown and the questions were asked. That wasn’t so bad. (a)    Who was the round little man in the above lines? (b)    Why did he come with the box of tools with dials and wires? (c) ...
Read more