Warning Disclaimer Notice:
The below answers and discussions are directly prepared by copy-paste from www.google.com, Googole AI Overview, https://gemini.google.com/app , https://chatgpt.com/,etc,
These are only for understanding to improve the concept only; not for answers. We have not checked these clearly. Some mistakes may be happened. We are not responsible for any wrong information and answers. Please check yourself, yourselves and discuss with your expert teachers for the final approval or accepted the answers.
Database Part-II
MySQL
CHAPTER 3
http://dev.mysql.com/downloads.
Exercise
I. MULTIPLE CHOICE QUESTIONS:
1. The command used
to modify the content of a table is:
a) ALTER TABLE
b) SELECT
c) UPDATE
Answer:
The correct option is
✅ c) UPDATE.
Explanation
The UPDATE command
is a Data Manipulation Language (DML) statement specifically used to modify
the content (existing records or data) within a table. It
allows you to change specific values in one or more rows based on a
condition.
Why other options are incorrect
·
❌ a) ALTER
TABLE: This is a Data Definition Language (DDL) command used to modify
the structure or definition of a table (such as adding,
deleting, or renaming columns), rather than the data stored inside it.
·
❌ b) SELECT:
This command is used solely to retrieve or fetch data from a
database; it does not perform any modifications to the table structure or its
content.
2. The command used to display
the table structure is:
a) DISPLAY
b) STRUCTURE
c) DESCRIBE
Answer:
The correct option is
✅ c) DESCRIBE.
Explanation
The DESCRIBE command
(often abbreviated as DESC) is specifically used in database
management systems like MySQL, Oracle, and MariaDB to display a table's
structure. This includes metadata such as:
·
Column
names (field names).
·
Data
types (e.g., INT,
VARCHAR).
·
Constraints (e.g., Primary Key, Not Null).
·
Default
values for
columns.
Why other options are incorrect
·
❌ a) DISPLAY:
While "display" describes the action of showing
data, it is not a standard SQL keyword for viewing table definitions. In some
systems (like FoxPro or dBase), it was used to show records, but it is not the
standard command for table structures in modern SQL databases.
·
❌ b) STRUCTURE:
Although this is what the user wants to see, "STRUCTURE" is not a
built-in command in SQL to retrieve table schemas. To see the structure, you
must use DESCRIBE or system-specific alternatives like SHOW COLUMNS (MySQL) or \d (PostgreSQL).
3. A table name should begin
with:
a) Number
b) Alphabet
c) Symbol
Answer:
A table name should
begin with:
b) Alphabet
Explanation
·
Rules: In SQL and most database management
systems (DBMS), table names must begin with a letter (a-z or A-Z).
·
Alternative: They can sometimes begin with an
underscore (_), but generally, they should not begin with a
number.
·
Reasons: Starting with a letter avoids syntax
errors, as numbers or symbols at the beginning can confuse the parser,
especially if the table name is not properly quoted.
·
Allowed
characters: Subsequent
characters can include letters, numbers, and underscores.
·
Best
Practice: Use descriptive
names that start with a letter (e.g., customers instead of 1st_customers).
In MySQL, a table name
should begin with an Alphabet [1].
According to the
official MySQL documentation:
·
Identifiers (including
table names) can begin with a letter (A-Z, a-z) or a number (0-9).
·
They can also begin
with $ or _.
·
However, if an
identifier is a number, it must not be used unless quoted [1].
To ensure clarity and
avoid potential issues (especially when working with different modes or tools),
it is standard practice to begin all table names with a letter.
4. The command used to delete the
database physically:
a) DELETE
b) ERASE
c) DROP
Answer:
The correct command to
physically delete a database is c) DROP.
Here is an explanation
of the options:
·
a)
DELETE: This command is used
to delete rows from a table within a database. It removes the
data but keeps the table structure intact [1].
·
b)
ERASE: This is not a
standard SQL command.
·
c)
DROP: This command is a
Data Definition Language (DDL) operation used to delete objects from the
database entirely, including the database structure (schema) and all associated
physical data files [1]. The standard syntax is DROP DATABASE
database_name;.
5.
This wildcard character allows finding a match for any string of any length,
including zero length:
a)
*
b)
%
c)
#
The answer is b) % when
used in SQL, or **a) *** when used in standard command-line interfaces, regular
expressions, or file system searches.
The specific character
depends on the context or programming language:
·
% is the standard wildcard character used
in SQL LIKE operations to match any string of zero
or more characters [1].
·
*** (asterisk)** is
the most common wildcard used across various systems, including file system
searches (e.g., in Windows or Linux command lines), regular expressions, and
many programming languages, to represent zero or more characters.
For a general question
without specifying the environment, both are widely accepted answers depending
on the domain. However, in many multiple-choice contexts, % specifically
refers to the SQL application.
6.
This operator displays only those records that do not satisfy the specified
condition,
a)
AND b) OR c) NOT
Answer:
The correct answer
is c) NOT.
The NOT operator is used in logical expressions
and database queries to reverse the result of a condition, displaying only
those records that do not satisfy the specified criteria. For example, a search
for NOT
city = 'New York' would return all
records where the city is anything except New York.
II. FILL IN THE BLANKS:
1. MySQL is named after co-founder Michael Widenius's daughter,
_______.
2. The number of rows denotes the _________ of the table.
3. The number of _________ denotes the Degree of the table.
4. ____________ words are not allowed in a table name.
5. A MySQL statement is terminated by a ___________.
6. The underscore wildcard allows finding a match for any ________
character.
Answer:
1.
My (or My); 2. Cardinality;
3. Columns (or Attributes);
4. Reserved; 5. Semicolon (;); 6. Single (or One).
Here are the completed sentences for
your MySQL fill-in-the-blanks:
1.
MySQL is named after co-founder Michael
Widenius's daughter, My.
2.
The number of rows denotes the Cardinality of
the table.
3.
The number of Columns (or
Attributes) denotes the Degree of the table.
4.
Reserved words are not
allowed in a table name.
5.
A MySQL statement is terminated by a Semicolon (;).
6.
The underscore wildcard (_) allows finding a match
for any Single character.
Note: "Cardinality" refers to the number of rows (tuples),
while "Degree" refers to the number of columns (attributes) in
relational database theory. SQL reserved words, like SELECT or DELETE, cannot be used as
table names. The underscore (_) in LIKE patterns matches exactly one character, while the percent
sign (%) matches multiple.
1. My2. Cardinality3. Columns (or Attributes)4. Reserved5. Semicolon (;)6. Single (or One)
III. ANSWER THE FOLLOWING QUESTIONS:
1. Who were the
developers of MySQL?
Answer:
MySQL was developed by
Swedish company MySQL AB, founded by Michael "Monty" Widenius, David Axmark,
and Allan Larsson,
who began development in 1994 and released the first version in May 1995,
creating a fast, open-source database system.
Key Figures:
·
Michael "Monty" Widenius: The
primary developer and "Father of MySQL".
·
David Axmark & Allan Larsson: Co-founders
who joined Widenius in the creation and promotion of MySQL.
Company & History:
·
They formed MySQL AB in 1995 to further
develop the database.
·
It was designed as a faster, more flexible,
and open-source alternative to the mSQL database, quickly becoming popular for
web applications.
2. Why is MySQL becoming
so popular? Give two reasons.
Answer:
MySQL is extremely
popular due to its open-source nature and cost-effectiveness,
as well as its ease of use and broad compatibility. These factors
make it a versatile choice for both individual developers and large-scale
enterprises like Netflix, Uber, and Facebook.
Key Reasons for MySQL's Popularity
·
Open
Source and Cost-Effective
MySQL is freely available to use under the GNU General Public License (GPL),
which eliminates expensive licensing fees and drastically lowers initial
development costs. This open-source model has also fostered a large, vibrant
global community that contributes to its continuous improvement, provides
extensive documentation, and offers free support through forums and tutorials.
·
Ease
of Use and Compatibility
MySQL is known for being easy to install, configure, and manage, with a
straightforward SQL syntax that makes it accessible for beginners and efficient
for experienced developers. Furthermore, it is highly compatible with nearly
all operating systems and programming languages (including PHP, Python, Java,
and more) and is a core component of the popular LAMP (Linux, Apache, MySQL,
PHP/Perl/Python) development stack, facilitating seamless integration into a
wide variety of development environments.
3. What is a
constraint? Name any two constraints.
Answer:
A constraint is a rule
or limitation that restricts what can be done, ensuring something stays within
defined boundaries, like ensuring data accuracy or limiting project scope; two
common examples are a Time Constraint (limited time to
finish) or a Primary Key Constraint (unique ID for
each database record). Constraints control acceptable actions or values to
maintain integrity and order.
Examples of
Constraints:
·
Time Constraint: A deadline for
completing a task, restricting how long you have to work on it.
·
Resource Constraint:
Limited budget, materials, or people available for a project.
·
Primary Key Constraint: In
databases, ensures each row has a unique identifier (like a User ID) and can't
be empty.
·
Not Null Constraint: In
databases, ensures a specific column must always have a value, preventing blank
entries.
·
Check Constraint: In databases,
enforces specific conditions on data, like ensuring a number is positive.
4. Give examples of
DML commands?
Answer:
DML (Data Manipulation
Language) commands are used to manage data within database objects, primarily
in SQL, with core examples being SELECT (retrieve data), INSERT (add new rows), UPDATE (modify existing data), and DELETE (remove rows). Other related DML
commands include MERGE (combine
insert/update) and CALL (execute stored
procedures).
Core DML Commands in SQL
·
SELECT: Retrieves data from
one or more tables.
o Example: SELECT * FROM Employees WHERE Department = 'Sales';.
·
INSERT: Adds new rows
(records) into a table.
o Example: INSERT INTO Employees (EmpID, Name, Department) VALUES (101,
'Alice', 'Marketing');.
·
UPDATE: Modifies existing
data in a table.
o Example: UPDATE Employees SET Salary = 55000 WHERE EmpID = 101;.
·
DELETE: Removes rows from a
table based on a condition.
o Example: DELETE FROM Employees WHERE EmpID = 101;.
Other DML Commands
·
MERGE: Performs INSERT and UPDATE operations in a single command, synchronizing tables.
·
CALL: Executes stored
procedures, which can contain multiple DML operations.
·
TRUNCATE TABLE: Removes all rows from a table quickly (often considered DDL
but can function like bulk DELETE).
5. What are the
characteristics by which you can determine the data type of MySQL?
Answer:
We can determine the
appropriate MySQL data type for a column by evaluating characteristics such
as the domain
of the data, the range of values, storage requirements, and whether the length
is fixed or variable.
Key characteristics to
consider when choosing a data type:
·
Domain of the Data: The primary
consideration is what kind of values the column will hold.
o
Numeric: For numbers used in
mathematical functions (e.g., product quantity, price).
o
String/Text: For letters, numbers,
and special characters not used in calculations (e.g., names, addresses,
descriptions).
o
Date/Time: For temporal data
(e.g., birth dates, event timestamps).
o
Binary: For data like images or files.
o
Spatial/JSON: For geographic
coordinates or semi-structured data.
·
Range of Values: Each data type has a
specific minimum and maximum value it can store. Choosing a type that fits the
expected range (e.g., TINYINT for small numbers, BIGINT for very large integers)
helps optimize storage and prevents errors when inserting data.
·
Storage Requirements: Different data types
use varying amounts of storage space. Selecting the most space-efficient type
for your needs (e.g., using SMALLINT instead of BIGINT if the range
allows) can improve database performance.
·
Fixed or Variable Length:
o
Fixed-length types (CHAR, BINARY) occupy the same amount
of space regardless of the actual data size, which can be faster for
consistent-length data but wastes space if values are often shorter than the
defined length.
o
Variable-length types (VARCHAR, VARBINARY, TEXT, BLOB) only use the space
needed for the data stored, plus a small overhead. These are more
space-efficient for data that varies greatly in length.
·
Precision and Scale: For decimal numbers,
you need to determine the total number of digits (M, precision) and the
number of digits allowed after the decimal point (D, scale). The DECIMAL type provides
exact fixed-point storage, which is ideal for financial data, while FLOAT and DOUBLE store approximate
values.
·
Character Set and Collation: For string types, you
must consider the character encoding (e.g., utf8mb4) and the collation
rules that determine how data is sorted and compared.
By carefully evaluating these characteristics, a developer can
select the most appropriate data type to ensure data integrity, optimize
storage, and enhance query performance. The MySQL documentation on Data Types provides a comprehensive
guide to all available options.
6. What is the query
to display the table structure?
Answer:
The query to display a
table's structure depends on the specific database system you are using.
The most common commands are DESCRIBE and SHOW COLUMNS,
or a system-specific stored procedure.
Here are the specific
queries for popular database systems:
MySQL and Oracle
You can use the DESCRIBE (or DESC as a shorthand) command in both MySQL and Oracle to get
details like column names, data types, nullability, and key constraints.
sql
DESCRIBE
table_name;
-- or
DESC
table_name;
An alternative in
MySQL is the SHOW
COLUMNS command:
sql
SHOW
COLUMNS FROM
table_name;
To get the exact CREATE TABLE statement used to define the table, you
can use:
sql
SHOW
CREATE TABLE
table_name;
SQL Server
SQL Server uses system
stored procedures or standard schema views to show table structure:
·
Use the built-in
system stored procedure sp_help for a
comprehensive description:
sql
EXEC sp_help 'table_name';
·
Alternatively, you can
query the INFORMATION_SCHEMA.COLUMNS view, which is an ANSI SQL standard
approach that works across many systems:
sql
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name';
PostgreSQL
In the psql command-line interface, you can use a
shortcut command:
sql
\d table_name
-- For more detailed
information, use:
\d+ table_name
We can also use the
standard INFORMATION_SCHEMA query as shown for SQL Server above.
Note: In all the above examples, you should
replace table_name with the actual name of your table.
7. What is the query
to display all the records in a table?
Answer:
To display all records
(rows and columns) from a table in SQL, the query is SELECT * FROM
table_name;, replacing table_name with your actual table's name; the
asterisk (*) acts as a wildcard for all columns, and FROM specifies the table source.
sql
SELECT
* FROM your_table_name;
Explanation:
·
SELECT: The SQL command to
retrieve data.
·
*: A wildcard that
means "all columns".
·
FROM: Specifies which
table to get the data from.
·
your_table_name:
The name of the table you want to query.
8. List the
Arithmetic Operators used in MySQL.
Answer:
MySQL supports the
standard arithmetic operators, including addition, subtraction (and unary
minus), multiplication, and both floating-point and integer division, as well
as modulo operations.
The arithmetic
operators used in MySQL are:
·
+ : Addition
·
- : Subtraction
(binary operator) or change the sign of the argument (unary operator)
·
* : Multiplication
·
/ : Division
(floating-point result)
·
% or MOD : Modulo (returns the remainder of a
division operation)
·
DIV : Integer
division (discards any fractional part to the right of the decimal point)
9. List the
Relational Operators used in MySQL.
Answer:
MySQL uses
several relational operators (also known as comparison operators)
to compare values, primarily in the WHERE clause of a query. These operators evaluate conditions and
return a boolean result (TRUE, FALSE, or NULL).
Here is a list of the
main relational operators used in MySQL:
·
=: Equal to
·
<> or !=: Not equal to.
·
>: Greater than
·
<: Less than
·
>=: Greater than or
equal to
·
<=: Less than or equal
to
·
<=>:
NULL-safe equal to, which handles NULL comparisons differently than the = operator.
MySQL also provides
other comparison-related operators and functions for filtering data:
·
BETWEEN ... AND ...: Checks if a value is within a range.
·
IN (...):
Checks if a value is present in a list.
·
IS NULL:
Checks if a value is NULL.
·
IS NOT NULL:
Checks if a value is not NULL.
·
LIKE: Used for simple
pattern matching.
·
REGEXP or RLIKE: Used for regular expression pattern
matching.
For more details and
examples, you can refer to the MySQL 8.4 Reference Manual on comparison
operators.
10. Differentiate between COUNT(*) and COUNT.
Answer:
In SQL, the key
difference is that COUNT(*) counts all rows in
a table, including those with NULL values, whereas COUNT(column_name) only counts rows where the specified column contains a
non-NULL value. The term
"COUNT" by itself is a function that requires an argument,
either * or a specific
expression/column name.
COUNT(*)
·
Functionality: Returns the total
number of rows in the result set or a specified table, regardless of the data
within those rows.
·
NULL Values: It includes rows
that contain one or more NULL values in other columns.
·
Purpose: Use COUNT(*) when you want the
absolute total row count of a table or query result.
·
Performance: In modern database
systems like MySQL, SQL Server, and Oracle, the
query optimizer is highly efficient and there is typically no significant
performance difference between COUNT(*) and COUNT(1). In some specific
scenarios, like with MyISAM tables in MySQL, the row count is stored as
metadata, making COUNT(*) very fast.
COUNT(column_name)
·
Functionality: Evaluates a
specified column for each row and returns the count of non-NULL values found in
that column.
·
NULL Values: It explicitly
ignores any rows where the specified column_name is NULL.
·
Purpose: Use COUNT(column_name) when
you need to know how many records have data in a specific field,
as opposed to the total number of records overall.
·
Variations: You can also
use COUNT(DISTINCT
column_name) to count only the unique, non-NULL values in a
column.
Summary
Table
|
Feature |
|
|
|
Counts all rows? |
Yes |
No (only non- |
|
Includes |
Yes |
No (ignores them) |
|
Primary Use |
Getting the total row count |
Counting the number of existing values in a
specific column |
In short, if you simply
need the total number of records, COUNT(*) is the standard and most intuitive choice.
11. What are the
rules for naming a table in MySQL?
Answer:
MySQL table naming
rules require names to be unique within a database, up to 64 characters, and
typically use lowercase letters, numbers, and underscores, avoiding spaces and
reserved words like SELECT or ORDER, though quoting allows more flexibility for
special characters or case sensitivity; consistency in lowercase/snake_case is
a strong convention for readability.
Mandatory Rules (Technical)
·
Uniqueness: Table names must be unique within their
database.
·
Length: Up to 64 characters (though some older
systems might have different limits).
·
Characters
(Unquoted): Alphanumeric
characters (a-z, A-Z, 0-9), underscore (_), and dollar sign ($).
·
Case
Sensitivity: Depends on the
operating system and MySQL configuration, but generally treated as
case-sensitive, making lowercase recommended.
·
Reserved
Words: Avoid using SQL
reserved keywords (e.g., ORDER, SELECT, INSERT) as names to prevent syntax errors, or use backticks (`) to quote them if necessary.
Recommended Conventions (Best
Practices)
·
Lowercase
& Snake Case: Use lowercase
letters and underscores for readability (e.g., customer_orders).
·
Descriptive: Names should clearly indicate the table's
purpose (e.g., products instead of tbl1).
·
No
Spaces: Replace spaces with
underscores.
·
Singular/Plural
Consistency: Decide on singular
(e.g., customer) or plural (e.g., customers) and stick with it.
·
Avoid
Abbreviations: Use full words for
clarity.
Examples
·
Good: users, product_details, order_items.
·
Bad: User, Products_Details, ORDER (reserved word).
12. Explain the five categories of SQL commands?
Answer:
SQL commands are
categorized into five main types, each serving a distinct purpose in
managing and interacting with relational databases. The categories are:
·
Data
Definition Language (DDL)
·
Data
Manipulation Language (DML)
·
Data
Query Language (DQL)
·
Data
Control Language (DCL)
·
Transaction
Control Language (TCL)
1. Data Definition Language (DDL)
DDL commands are used
to define or modify the structure (schema) of the database and its objects,
such as tables, indexes, and views. These commands are auto-committed, meaning
changes are saved permanently and cannot be rolled back.
·
CREATE: Creates a new
database, table, index, or other object.
·
ALTER: Modifies the
structure of an existing database object, such as adding, dropping, or
modifying columns in a table.
·
DROP: Deletes objects
(e.g., a table or database) permanently from the database.
·
TRUNCATE:
Removes all records from a table quickly but keeps the table structure
intact.
2. Data Manipulation Language (DML)
DML commands are used
to manage and manipulate the data within the database tables. These commands
are not auto-committed and can be rolled back.
·
INSERT: Adds new rows
(records) of data into a table.
·
UPDATE: Modifies existing
data within a table.
·
DELETE: Removes one or more
rows from a table.
3. Data Query Language (DQL)
DQL commands are used
specifically for querying and retrieving data from the database based on
specified conditions.
·
SELECT: Retrieves data from
one or more tables. This is often used with clauses like WHERE, GROUP BY, and JOIN to filter and
structure the results.
4. Data Control Language (DCL)
DCL commands are used
to control access, permissions, and security within the database. They are
typically used by database administrators to manage user privileges.
·
GRANT: Gives specific
access privileges or permissions to a user or role.
·
REVOKE: Removes previously
granted privileges from a user or role.
5. Transaction Control Language
(TCL)
TCL commands manage
transactions in the database to ensure data integrity and consistency. They are
used in conjunction with DML commands.
·
COMMIT: Permanently saves
all changes made during the current transaction.
·
ROLLBACK:
Undoes all changes made during the current transaction since the last COMMIT or ROLLBACK.
·
SAVEPOINT:
Sets a point within a transaction to which you can later roll back, without
rolling back the entire transaction.
IV. PRACTICAL EXERCISE
1. Create a Table ‘customer’ in a database named ‘Klubmart’ with
the following fields:
|
Column Name |
Data Type |
Size |
Constraints |
|
CustomerID |
INT |
|
PRIMARY KEY |
|
Customer Name |
VARCHAR |
20 |
NOT NULL |
|
CustomerMobile |
VARCHAR |
11 |
NOT NULL |
|
CustomerCategory |
CHAR |
1 |
It is either X, Y
or Z |
2. Display the structure of the table.
3. Insert five records in the table.
4. Display all the records.
5. Display the CustomerName & CustomerMobile.
6. Display the CustomerName whose CustomerCategory is ‘X’.
7. Display the CustomerID whose CustomerCategory is ‘Y’ OR ‘Z’.
8. Display the distinct CustomerCategory from the table.
V. SOLUTION FROM THE BOOK:
1. The MySQL command to create the database is:
CREATE DATABASE Klubmart;
To use the Database Klubmart, type:
USE Klubmart;
To create the table ‘customer’ in the database ‘Klubmart’:
CREATE TABLE customer
(CustomerID INT PRIMARY KEY,
CustomerName VARCHAR (20) NOT NULL,
CustomerMobile VARCHAR (10) NOT NULL,
CustomerCategory CHAR);
2. To display the structure of the table type the command:
DESCRIBE customer;
3. To insert the record type the following command:
INSERT INTO customer values (1, ‘Atul’,’9435110011’, ‘X’);
INSERT INTO customer values (2, ‘Ashwini’,’9864078011’, ‘Y’);
INSERT INTO customer values (3, ‘Anjana’,’7086219344’, ‘Z’);
INSERT INTO customer values (4, ‘Archana’,’9435987611’, ‘Y’);
INSERT INTO customer values (5, ‘Amresh’,’9435110011’, ‘X’);
4.
SELECT * FROM customer;
5.
SELECT CustomerName, CustomerMobile FROM customer;
6.
SELECT CustomerName FROM customer WHERE CustomerCategory = ‘X’;
7.
SELECT CustomerID FROM customer WHERE CustomerCategory = ‘Y’ OR
CustomerCategory
= ‘Z’;
8.
SELECT DISTINCT(CustomerCategory) FROM customer;
AI SOLUTION Answer:
The following SQL commands can be used to create the
database and the specified customer table.
-- 1. Create the database named 'Klubmart'
CREATE DATABASE Klubmart;
GO
-- 2. Switch context to use the new database
USE Klubmart;
GO
-- 3. Create the 'customer' table with the specified constraints
CREATE TABLE customer (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(20) NOT NULL,
CustomerMobile VARCHAR(11) NOT NULL,
CustomerCategory CHAR(1) CHECK (CustomerCategory IN ('X', 'Y', 'Z'))
);
GO
Explanation of the Constraints
·
PRIMARY KEY:
Ensures that CustomerID uniquely identifies each row and does
not allow null values.
·
NOT NULL:
Ensures that the CustomerName and CustomerMobile fields must contain a value and cannot
be empty.
·
CHECK (CustomerCategory IN ('X', 'Y', 'Z')): Enforces the business rule that the CustomerCategory can only contain one of the specified
values ('X', 'Y', or 'Z').
2.
Display the structure of the table.
Answer:
2. Display the Structure of the
Table
To display the
structure (schema) of the created customer table, you can use the DESCRIBE or its shorthand DESC command.
sql
-- Display the
structure of the 'customer' table
DESCRIBE
customer;
-- or the shorthand
version
DESC
customer;
When you run the DESCRIBE customer; command, the output will typically show
the following structure:
|
Field |
Type |
Null |
Key |
Default |
Extra |
|
CustomerID |
int |
NO |
PRI |
NULL |
|
|
CustomerName |
varchar(20) |
NO |
NULL |
||
|
CustomerMobile |
varchar(11) |
NO |
NULL |
||
|
CustomerCategory |
char(1) |
NO |
NULL |
(Note: The exact
output column names might vary slightly depending on the specific SQL database
system you are using, but the core information (Field, Type, Null, Key) will be
present).
3.
Insert five records in the table.
Answer:
3. Insert Five Records
This command inserts
five sample records into the customer table using a single INSERT INTO statement with multiple value lists:
sql
INSERT
INTO customer (CustomerID, CustomerName,
CustomerMobile, CustomerCategory)
VALUES
(1,
'Alice', '1234567890',
'X'),
(2,
'Bob', '0987654321',
'Y'),
(3,
'Charlie', '1122334455',
'Z'),
(4,
'David', '2233445566',
'X'),
(5,
'Eve', '3344556677',
'Y');
Note: The mobile number values are examples and
should be adjusted to fit specific country code requirements if necessary, but
follow the specified VARCHAR(11) size.
4.
Display all the records.
Answer:
4. Display All Records
Finally, display all
the records from the customer table using
the SELECT
* query:
sql
SELECT
* FROM customer;
This will produce an
output similar to the following, depending on the data inserted:
|
CustomerID |
CustomerName |
CustomerMobile |
CustomerCategory |
|
1 |
Alice |
1234567890 |
X |
|
2 |
Bob |
0987654321 |
Y |
|
3 |
Charlie |
1122334455 |
Z |
5.
Display the CustomerName & CustomerMobile.
Answer:
5. Display the CustomerName & CustomerMobile
Finally, here is the
SQL query to select and display only the CustomerName and CustomerMobile columns.
sql
SELECT
CustomerName, CustomerMobile FROM customer;
Result of the SELECT query (using the sample data):
|
CustomerName |
CustomerMobile |
|
Alice |
12345678901 |
|
Bob |
98765432109 |
|
Charlie |
55512345678 |
6.
Display the CustomerName whose CustomerCategory is ‘X’.
Answer:
To create the database,
table, and perform the requested query in SQL, use the following commands:
-- 1. Create the database named 'Klubmart'
CREATE DATABASE Klubmart;
-- Use the 'Klubmart' database
USE Klubmart;
-- 2. Create the 'customer' table with the specified constraints
CREATE TABLE customer (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(20) NOT NULL,
CustomerMobile VARCHAR(11) NOT NULL,
CustomerCategory CHAR(1) CHECK (CustomerCategory IN ('X', 'Y', 'Z'))
);
-- Optional: Insert sample data into the 'customer' table for demonstration purposes
INSERT INTO customer (CustomerID, CustomerName, CustomerMobile, CustomerCategory) VALUES
(1, 'Alice', '12345678901', 'X'),
(2, 'Bob', '09876543210', 'Y'),
(3, 'Charlie', '11223344556', 'X'),
(4, 'David', '22334455667', 'Z');
-- 6. Display the CustomerName whose CustomerCategory is 'X'
SELECT CustomerName
FROM customer
WHERE CustomerCategory = 'X';
7.
Display the CustomerID whose CustomerCategory is ‘Y’ OR ‘Z’.
Answer:
To create the table and
display the requested records using SQL, you would use the following commands:
-- 1. Create the database (if it doesn't exist, this step might vary slightly based on your specific database system, like MySQL, PostgreSQL, etc.)
CREATE DATABASE IF NOT EXISTS Klubmart;
-- 2. Use the newly created database
USE Klubmart;
-- 3. Create the 'customer' table with the specified constraints
CREATE TABLE customer (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(20) NOT NULL,
CustomerMobile VARCHAR(11) NOT NULL,
CustomerCategory CHAR(1) CHECK (CustomerCategory IN ('X', 'Y', 'Z'))
);
-- Optional: Insert some sample data to test the query
INSERT INTO customer (CustomerID, CustomerName, CustomerMobile, CustomerCategory) VALUES
(101, 'Alice Smith', '5551234567', 'X'),
(102, 'Bob Johnson', '5559876543', 'Y'),
(103, 'Charlie Brown', '5554567890', 'Z'),
(104, 'David Lee', '5556543210', 'Y');
-- 7. Display the CustomerID whose CustomerCategory is ‘Y’ OR ‘Z’
SELECT
CustomerID
FROM
customer
WHERE
CustomerCategory = 'Y' OR CustomerCategory = 'Z';
-- Alternatively, using the IN operator for brevity
SELECT
CustomerID
FROM
customer
WHERE
CustomerCategory IN ('Y', 'Z');
The SELECT statement in step 7 will return the CustomerID for records where the CustomerCategory is either 'Y' or 'Z', effectively
completing the specified task.
8.
Display the distinct CustomerCategory from the table.
Answer:
To create the database and table, and then display the
distinct CustomerCategory, you would use the following SQL commands:
-- 1. Create the database 'Klubmart' (if it doesn't already exist)
CREATE DATABASE IF NOT EXISTS Klubmart;
-- 2. Switch to the 'Klubmart' database
USE Klubmart;
-- 3. Create the table 'customer' with the specified fields and constraints
CREATE TABLE customer (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(20) NOT NULL,
CustomerMobile VARCHAR(11) NOT NULL,
CustomerCategory CHAR(1) CHECK (CustomerCategory IN ('X', 'Y', 'Z'))
);
-- Optional: Insert some sample data to test the distinct category query
INSERT INTO customer (CustomerID, CustomerName, CustomerMobile, CustomerCategory) VALUES
(1, 'Alice', '1234567890', 'X'),
(2, 'Bob', '0987654321', 'Y'),
(3, 'Charlie', '1122334455', 'X'),
(4, 'David', '5544332211', 'Z');
-- 8. Display the distinct CustomerCategory from the table
SELECT DISTINCT CustomerCategory FROM customer;
2.
Create a table Worker in a database named Organisation. Enter the records given
in the table below.
|
Worker_ID |
First_Name |
Last_Name |
Salary |
Joining_Date |
Department |
|
001 |
Rebecca |
Doungel |
40000 |
2014-02-20 |
HR |
|
002 |
Jahnavi |
Borthakur |
68000 |
2014-06-11 |
Admin |
|
003 |
Digvijay |
Goswami |
50000 |
2014-02-20 |
HR |
|
004 |
Krishang |
Shandilya |
50000 |
2014-02-20 |
Admin |
|
005 |
Jaspal |
Bhatti |
55000 |
2014-06-11 |
Admin |
|
006 |
Kankana |
Devi |
20000 |
2014-06-11 |
Account |
|
007 |
Shristi |
Goswami |
75000 |
2014-01-20 |
Account |
|
008 |
Geeta |
Sharma |
90000 |
2014-04-11 |
Admin |
Answer:
2. Create and
populate the Worker table
Step 1:
Create the database and table
Create the
database named Organisation and the Worker table with appropriate data types
for each column.
CREATE
DATABASE Organisation;
USE
Organisation;
CREATE
TABLE Worker (
Worker_ID INT PRIMARY KEY,
First_Name VARCHAR(50),
Last_Name VARCHAR(50),
Salary INT,
Joining_Date DATE,
Department VARCHAR(50)
);
Step 2:
Insert the records
Insert all
the provided records into the Worker table using INSERT INTO statements.
INSERT
INTO Worker VALUES (001, 'Rebecca', 'Doungel', 40000, '2014-02-20', 'HR');
INSERT
INTO Worker VALUES (002, 'Jahnavi', 'Borthakur', 68000, '2014-06-11', 'Admin');
INSERT
INTO Worker VALUES (003, 'Digvijay', 'Goswami', 50000, '2014-02-20', 'HR');
INSERT
INTO Worker VALUES (004, 'Krishang', 'Shandilya', 50000, '2014-02-20', 'Admin');
INSERT
INTO Worker VALUES (005, 'Jaspal', 'Bhatti', 55000, '2014-06-11', 'Admin');
INSERT
INTO Worker VALUES (006, 'Kankana', 'Devi', 20000, '2014-06-11', 'Account');
INSERT
INTO Worker VALUES (007, 'Shristi', 'Goswami', 75000, '2014-01-20', 'Account');
INSERT
INTO Worker VALUES (008, 'Geeta', 'Sharma', 90000, '2014-04-11', 'Admin');
Question:
Write the commands to do the following:
1.
Display Worker_ID and Joining_Date of Admin Department.
2.
Display records in ascending order of Salary.
3.
Display records having Salary in the range 50000 to 90000.
4.
Display the record of persons who First_Name ends with ‘a’.
5.
Display the records of HR and Account Department.
6.
The worker is given bonus as 1% of the Salary. Display the First_Name,
Last_Name and Bonus.
7.
Display the sum, maximum, minimum and average Salary.
8.
Display the count of records where Department = ‘HR’.
Solution:
1.
SELECT Worker_ID, Joining_Date FROM Worker WHERE Department = ‘Admin’;
2.
SELECT * FROM Worker ORDER BY Salary;
3.
SELECT * FROM Worker BETWEEN 50000 AND 90000;
4.
SELECT * FROM Worker WHERE First_Name LIKE ‘%a’;
5.
SELECT * FROM Worker WHERE Department = ‘HR’ OR Department = ‘Account’;
6.
SELECT First_Name, Last_Name, 0.01 * Salary AS “BONUS” FROM Worker;
7.
SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary);
8.
SELECT COUNT(*) FROM Worker WHERE Department = ‘HR’;