MySQL SQL Revision Tour
SUMMARY
The basic elements of MySQL SQL are: literals, datatypes, nulls and comments.
Literals are fixed data values.
Data types of MySQL SQL include: NUMERIC (INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, FLOAT(M, D), DOUBLE(M. D) AND DECIMAL(M, D), DATE and TIME (DATE, DATETIME, TIMESTAMP, TIME and YEAR(M) and STRING (CHAR(M). VARCHAR(M), BLOB or TEXT, TINYBLOB or TINYTEXT, MEDIUMBLOB or MEDIUMTEXT and ENUM).
Column having no value is said to have NULL value.
The SELECT command of SQL lets you make queries on the database.
The DISTINCT keyword eliminates redundant data.
To perform calculations, the expressions can be written along with SELECT without specifying any table name.
A small work table Dummy can be used for making simple calculations.
The curdate pseudo-column returns the current system date.
The WHERE clause is used to select specific rows.
The logical operators OR (II), AND (&&) and NOT (!) are used to connect search conditions in the WHERE clause.
The BETWEEN operator is used for making range checks in queries. The IN operator is used for selecting values from a list of values.
The LIKE operator is used for making character comparisons using strings.
The null value in a column can be searched for in a table using IS NULL in the WHERE clouse
CREATE TABLE command is used to create tables in database.
INSERT INTO command is used to insert data in the table.
To insert data from other tables, subquery can be used inside INSERT INTO command.
Existing data in tables can be changed with UPDATE command.
Tuples in a table can be deleted using DELETE command. ALTER TABLE command is used to alter the definition of already created tables.
With ALTER TABLE, new columns can be added, existing columns can be redefined.
DROP TABLE command drops a table from a database.
Answer the Following Question :-
TOPIC – DBMS CONCEPTS
Q1. What is a database?
Ans. A database is a collection of interrelated data
Q2 What is data redundancy?
Ans. Data Redundancy means duplication of data in a database.
Q3 What is metadata ?
Ans. Data about data
Q4 What do you mean by degree and cardinality of a table?
Ans. Degree:- No. of columns with in a table.
Cardinality:- No. of rows within a table.
Q5 What is tuple?
Ans. Rows in a table are called tuple
Q6 What is domain?
Ans. Domain is the pool of values from which actual values appearing in a column aretaken.
Q7 What is the full form of DBMS?
Ans. Database Management System
Q8 Full form of DDL.
Ans. Data Definition Language
Q9 Full form of DML.
Ans. Data Manipulation Language
Q10 How many primary key can be present in a table ?
Ans. one
Q11 What is the full form of SQL?
Ans Structured Query Language
TOPIC - SQL BASICS ( DDL AND DML COMMANDS)
PART A , Section I
1 Which of the following is a DDL command?
a) SELECT b) ALTER c) INSERT d) UPDATE
Ans. b) ALTER
2 What is the minimum number of column required in MySQL to create table?
Ans. ONE (1)
3 The ____________command can be used to makes changes in the rows of a table in SQL.
Ans.
4 Which command is used to add new column in existing table?
Ans. ALTER TABLE
5 Which command is used to remove the table from database?
Ans. DROP TABLE
6 Which command is used to add new record in table?
Ans. INSERT INTO
7 In SQL, name the clause that is used to display the tuples in ascending order of an
attribute
Ans. ORDER BY
8 In SQL, what is the use of IS NULL operator?
Ans. IS NULL used to compare NULL values present in any column
9 Which of the following types of table constraints will prevent the entry of duplicate
rows? a)Unique b)Distinct c)Primary Key d)NULL
Ans. c) Primary Key
10 Which is the subset of SQL commands used to manipulate database structures, including
tables?
a.None of these
b.Both Data Definition Language (DDL) and Data Manipulation Language (DML)
c.Data Definition Language (DDL)
d.Data Manipulation Language (DML)
Ans. c.Data Definition Language (DDL)