Suppose your school management has decided to conduct cricket matches between students of Class XI and Class X

Exercises · Q4

Suppose your school management has decided to conduct cricket matches between students of Class XI and Class XII. Students of each class are asked to join any one of the four teams - Team Titan, Team Rockers, Team Magnet and Team Hurricane. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:

a) Create a database "Sports".

b) Create a table "TEAM" with following considerations:

i) It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.

ii) Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.

c) Using table level constraint, make TeamID as the primary key.

d) Show the structure of the table TEAM using a SQL statement.

e) As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:

Row 1: (1, Team Titan)

Row 2: (2, Team Rockers)

Row 3: (3, Team Magnet)

Row 3: (4, Team Hurricane)

f) Show the contents of the table TEAM using a DML statement.

g) Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types and constraints for each attribute.

Table: MATCH_DETAILS

MatchID	MatchDate	FirstTeamID	SecondTeamID	FirstTeamScore	SecondTeamScore
M1	2018-07-17	1	2	90	86
M2	2018-07-18	3	4	45	48
M3	2018-07-19	1	3	78	56
M4	2018-07-19	2	4	56	67
M5	2018-07-18	1	4	32	87
M6	2018-07-17	2	3	67	51

Punjab PsebTextbookSubjective· 4mImportance★★★★★

46% · 44/95 Questions

🔒 Locked · start free trial →

You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.

Start your 14-day free trial to unlock the full solution →

Concept understanding — Table Creation

Table Creation: The Art of Organising Information

Think of the last time you tried to find a specific contact in your phone. If all your contacts were just listed in one long, unbroken list — no groups, no alphabetical order, no search — you would have to scroll through every single name to find the one you need. That is the problem that table creation solves.

In everyday life, we create tables all the time without thinking about it. A timetable for your classes, a cricket scorecard, a menu with prices — each of these is a table. The instinct to arrange information into rows and columns is natural because it makes comparison and retrieval fast. Table creation, in the context of your studies, is simply the deliberate, systematic act of doing this with data so that it becomes meaningful and usable.

What Exactly Is a Table?

A table is a structured arrangement of data in rows and columns. Each row typically represents one complete record or observation — for example, one student, one month, one product. Each column represents a particular characteristic or variable of that record — for example, name, marks, price, date.

The power of a table lies in its ability to show relationships at a glance. When data is scattered in a paragraph, you have to read and mentally compare. When it is in a table, your eye can move vertically down a column or horizontally across a row, spotting patterns, extremes, and gaps instantly.

Note

In your NCERT textbooks for commerce and humanities subjects — especially in Economics, Statistics, and Business Studies — tables are the primary tool for presenting data before any analysis begins. They are not decorative; they are the foundation on which interpretation is built.

The Core Elements of a Table

Every proper table has a few essential parts. Understanding these will help you both read tables and create your own.

Title: A clear, concise heading that tells the reader what the table contains. It should answer the questions: what, where, and when. For example, "Monthly Sales of ABC Ltd., April–September 2023" is far better than just "Sales Data."
Column Headings (Captions): These label each column and describe the type of data it holds. They must be precise — "Revenue (in ₹ lakh)" is clearer than just "Revenue."
Row Headings (Stubs): These label each row, usually on the leftmost column. They identify what each row represents — for instance, the names of months, products, or regions.
Body of the Table: The actual data cells where numbers or text entries sit. This is the heart of the table.
Source Note: A line below the table stating where the data came from. This is crucial for credibility, especially in exam answers and project work.
Footnotes (if needed): Any clarification about a specific entry — for example, an asterisk explaining that a figure is an estimate.

Why Table Creation Matters for You

In your exams and assignments, you will often be asked to present raw data in tabular form. This is not busywork. The examiner wants to see that you can:

Identify the key variables in a given set of information.
Classify and group data logically.
Present it without ambiguity so that anyone reading can understand it immediately.

A well-created table can turn a confusing jumble of numbers into a clear story. For instance, if you are given the population figures of five states over three census years, a table lets you see at once which state grew fastest, which declined, and how each compares to the others. Without the table, you would have to hold all those numbers in your head. …

Start your 14-day free trial to unlock the full solution →

This question requires you to demonstrate your understanding of SQL DDL (Data Definition Language) for creating databases and tables, including various constraints, and DML (Data Manipulation Language) for inserting and retrieving data.

Here's how to approach each part:

(a) Create a database "Sports".

To create a new database, the CREATE DATABASE statement is used. After creation, it's good practice to select the database using USE so that subsequent operations are performed within it.

CREATE DATABASE Sports;
USE Sports;

(b) Create a table "TEAM" with following considerations:

It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.
Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.

(c) Using table level constraint, make TeamID as the primary key.

To create the TEAM table, we define TeamID as an INT and TeamName as VARCHAR.

TeamID needs to be unique and not null, which are properties of a primary key. The range constraint (1 to 9) is enforced using a CHECK constraint.
TeamName needs to be a string of at least 10 characters, which is also enforced using a CHECK constraint with the LENGTH() function.
The primary key constraint for TeamID is applied at the table level, meaning it's declared after all column definitions.

CREATE TABLE TEAM (
    TeamID INT NOT NULL,
    TeamName VARCHAR(50) NOT NULL,
    PRIMARY KEY (TeamID),
    CHECK (TeamID >= 1 AND TeamID <= 9),
    CHECK (LENGTH(TeamName) >= 10)
);

Note

VARCHAR(50) is chosen for TeamName to accommodate team names up to 50 characters. NOT NULL ensures that neither TeamID nor TeamName can be empty.

(d) Show the structure of the table TEAM using a SQL statement.

To display the structure (schema) of a table, including its columns, data types, and constraints, the DESCRIBE (or DESC) statement is used.

DESCRIBE TEAM;

(e) As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:

Row 1: (1, Team Titan)

Row 2: (2, Team Rockers)

Row 3: (3, Team Magnet)

Row 4: (4, Team Hurricane)

To add data into a table, the INSERT INTO statement is used. Multiple rows can be inserted efficiently in a single statement by listing them separated by commas.

INSERT INTO TEAM (TeamID, TeamName) VALUES
(1, 'Team Titan'),
(2, 'Team Rockers'),
(3, 'Team Magnet'),
(4, 'Team Hurricane');

(f) Show the contents of the table TEAM using a DML statement.

To retrieve all data from a table, the SELECT * FROM statement is used.

SELECT * FROM TEAM;

Expected Output:

TeamID	TeamName
1	Team Titan
2	Team Rockers
3	Team Magnet
4	Team Hurricane

(g) Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types and constraints for each attribute.

For the MATCH_DETAILS table:

MatchID: VARCHAR(3) is suitable for 'M1', 'M2', etc. It should be the PRIMARY KEY. …

Start your 14-day free trial to unlock the full solution →

This task involves creating a database and two tables, TEAM and MATCH_DETAILS, in SQL. It covers defining columns with appropriate data types, applying primary key, check, and foreign key constraints, and then inserting and displaying data.

(a) Create a database "Sports"

Before we can create any tables, we need a place to store them. A database acts as a container for all our related tables, views, procedures, and other database objects. The CREATE DATABASE statement is used for this purpose.

CREATE DATABASE Sports;
USE Sports;

Explanation:

CREATE DATABASE Sports;: This statement creates a new database named Sports.
USE Sports;: After creating the database, we need to select it as the current database so that all subsequent commands operate within it.

(b) Create a table "TEAM" with following considerations:

(c) Using table level constraint, make TeamID as the primary key.

Creating a table involves defining its name and the columns it will contain, along with their data types and any constraints.

TeamID: This column needs to store an integer value between 1 and 9 and uniquely identify each team.
- Data Type: INT is suitable for integer values.
- Constraint: The requirement for values between 1 and 9 can be enforced using a CHECK constraint.
- Primary Key: The requirement for unique identification makes TeamID a perfect candidate for a PRIMARY KEY. A primary key ensures that all values in the column are unique and not null, providing a unique identifier for each row. Applying it at the table level means defining it after all columns have been declared.
TeamName: This column stores the name of the team and must be a string of at least 10 characters.
- Data Type: VARCHAR(50) (or a similar length) is appropriate for storing variable-length strings. We choose a maximum length that is reasonable for team names.
- Constraint: A CHECK constraint can be used to ensure the length of the TeamName is not less than 10 characters.

CREATE TABLE TEAM (
    TeamID INT,
    TeamName VARCHAR(50),
    PRIMARY KEY (TeamID),
    CHECK (TeamID >= 1 AND TeamID <= 9),
    CHECK (LENGTH(TeamName) >= 10)
);

Explanation:

CREATE TABLE TEAM (...): This initiates the creation of a table named TEAM.
TeamID INT: Defines a column TeamID to store integer values.
TeamName VARCHAR(50): Defines a column TeamName to store strings, with a maximum length of 50 characters.
PRIMARY KEY (TeamID): This is a table-level constraint that designates TeamID as the primary key for the TEAM table. It ensures that each TeamID is unique and not null.
CHECK (TeamID >= 1 AND TeamID <= 9): This constraint ensures that any value inserted into TeamID must be between 1 and 9 (inclusive).
CHECK (LENGTH(TeamName) >= 10): This constraint ensures that the TeamName must have a length of at least 10 characters.

(d) Show the structure of the table TEAM using a SQL statement.

To view the structure (schema) of a table, including its columns, data types, and constraints, we can use the DESCRIBE (or DESC) statement or SHOW COLUMNS FROM statement.

DESCRIBE TEAM;

Expected Output (may vary slightly depending on the specific SQL RDBMS, e.g., MySQL, PostgreSQL):

Field	Type	Null	Key	Default	Extra
TeamID	int	NO	PRI	NULL
TeamName	varchar(50)	YES		NULL

Explanation:

DESCRIBE TEAM;: This statement displays the column names, their data types, whether they can contain NULL values, if they are part of a key (like PRI for primary key), and other information about the TEAM table's structure.

(e) As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:

To add new rows of data into an existing table, we use the INSERT INTO statement. We specify the table name, optionally the columns we are inserting into, and then the values for each column.

INSERT INTO TEAM (TeamID, TeamName) VALUES
(1, 'Team Titan'),
(2, 'Team Rockers'),
(3, 'Team Magnet'),
(4, 'Team Hurricane');

Explanation:

INSERT INTO TEAM (TeamID, TeamName) VALUES (...): This statement inserts data into the TEAM table. We explicitly list the columns TeamID and TeamName to ensure values are mapped correctly.
(1, 'Team Titan'), etc.: Each set of parentheses represents a row to be inserted, with values corresponding to the listed columns.

(f) Show the contents of the table TEAM using a DML statement.

To retrieve and display all the data stored in a table, we use the SELECT statement, which is a Data Manipulation Language (DML) command.

SELECT * FROM TEAM;

Expected Output:

TeamID	TeamName
1	Team Titan
2	Team Rockers
3	Team Magnet
4	Team Hurricane

Explanation:

SELECT * FROM TEAM;: This statement selects all columns (*) from the TEAM table and displays all the rows currently stored in it.

(g) Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types and constraints for each attribute.

This involves creating a new table MATCH_DETAILS and then populating it with the provided data. We need to carefully select data types and constraints for each column.

MatchID: Unique identifier for each match.
- Data Type: VARCHAR(5) (e.g., 'M1', 'M2').
- Constraint: PRIMARY KEY as it uniquely identifies each match.
MatchDate: Date of the match.
- Data Type: DATE.
FirstTeamID, SecondTeamID: These refer to TeamID from the TEAM table.
- Data Type: INT, matching the TeamID in the TEAM table.
- Constraint: FOREIGN KEY referencing TeamID in the TEAM table. This ensures referential integrity, meaning you cannot record a match with a TeamID that doesn't exist in the TEAM table.
FirstTeamScore, SecondTeamScore: Scores of the teams.
- Data Type: INT. Scores are typically non-negative, so a CHECK constraint could be added (e.g., CHECK (FirstTeamScore >= 0)), but it's not explicitly requested, so we'll keep it simple.

CREATE TABLE MATCH_DETAILS (
    MatchID VARCHAR(5) PRIMARY KEY,
    MatchDate DATE,
    FirstTeamID INT,
    SecondTeamID INT,
    FirstTeamScore INT,
    SecondTeamScore INT,
    FOREIGN KEY (FirstTeamID) REFERENCES TEAM(TeamID),
    FOREIGN KEY (SecondTeamID) REFERENCES TEAM(TeamID)
);

INSERT INTO MATCH_DETAILS (MatchID, MatchDate, FirstTeamID, SecondTeamID, FirstTeamScore, SecondTeamScore) VALUES
('M1', '2018-07-17', 1, 2, 90, 86),
('M2', '2018-07-18', 3, 4, 45, 48),
('M3', '2018-07-19', 1, 3, 78, 56),
('M4', '2018-07-19', 2, 4, 56, 67),
('M5', '2018-07-18', 1, 4, 32, 87),
('M6', '2018-07-17', 2, 3, 67, 51);

SELECT * FROM MATCH_DETAILS;

Explanation: …

Start your 14-day free trial to unlock the full solution →

←Previous questionQ3 — Consider the following MOVIE table and write the SQL queries based on …→Next questionQ5 — Using the sports database containing two relations (TEAM, MATCHDETAILS…

(a) Create a database "Sports"

(b) Create a table "TEAM" with following considerations:

(c) Using table level constraint, make TeamID as the primary key.

(d) Show the structure of the table TEAM using a SQL statement.

(e) As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:

(f) Show the contents of the table TEAM using a DML statement.

(g) Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types and constraints for each attribute.

🎓Unlock everything free for 14 days

Unlock everything free for 14 days