SQL Commands - Revision Notes

 CBSE Class 12 Computer Science (Python)

SQL Commands
Revision Notes

  • Some important points to remember-
  • 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(||), 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 clause.
  • CREATE TABLE commands 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.
  • A join is an SQL query that fetches records from two or more tables based on a condition.
  • An index is a data structure maintained by a database, that stores the sorted values within the index field and their location in actual data.
  • You can create indexes either by using INDEX clause of CREATE TABLE command or by using command CREATE INDEX.
  • The ORDER BY clause lets you arrange the result set in the order of single column, multiple columns, on the basis of an expression and as per custom sort order too.
  • The GROUP BY clause combines all those records that have identical value in a particular field or a group of fields.
  • GROUP BY clause is used to divide the result in groups.
  • A group within another group is called Nested Group.
  • Nested grouping can be done by providing multiple fields in the GROUP BY expression.
  • All fields containing a NULL value are considered to have a value and are grouped to have a value and are grouped with the fields containing non-NULL values.
  • The SELECT list of a group can include expressions returning single value per group or constants.
  • The HAVING clause is used to specify filtering condition for groups.
  • The difference between WHERE and HAVING clause is that WHERE conditions are applicable on individual rows whereas HAVING conditions are applicable on groups as formed by GROUP BY clause.