SQL Commands, Functions, and Operators Quiz

Study Notes

SQL Components

SQL consists of data types, operators, DDL (Data Definition Language) commands, and DML (Data Manipulation Language) commands.

DDL Commands

CREATE command: used to create tables
ALTER command: used to modify existing tables
DROP command: used to delete tables

DML Commands

SELECT command: used to retrieve data from tables
INSERT command: used to add new data to tables
DELETE command: used to delete data from tables
UPDATE command: used to modify existing data in tables

Query Clauses

ORDER BY clause: used to sort data in ascending or descending order
GROUP BY clause: used to group data based on one or more columns
HAVING clause: used to filter grouped data based on conditions

Views

A view is a virtual table based on the result of a query
DML operations can be performed on views

Functions

Numeric Functions

ABS: returns the absolute value of a number
MOD: returns the remainder of a division operation
FLOOR: returns the largest integer less than or equal to a number
CEIL: returns the smallest integer greater than or equal to a number
TRUNC: returns the truncated value of a number
SQRT: returns the square root of a number
SIGN: returns the sign of a number (1 if positive, -1 if negative, 0 if zero)
SIN, COS, LOG, EXP: returns the sine, cosine, logarithm, and exponential of a number, respectively
LEAST, GREATEST: returns the smallest or largest of two or more values

Group Functions

AVG: returns the average value of a set of numbers
MAX: returns the maximum value of a set of numbers
MIN: returns the minimum value of a set of numbers
SUM: returns the sum of a set of numbers
COUNT: returns the count of a set of values

Character Functions

LENGTH: returns the length of a string
LOWER, UPPER: returns the lowercase or uppercase version of a string
INITCAP: returns the capitalized version of a string
INSTR: returns the position of a substring within a string
SUBSTR: returns a subset of a string
LPAD, RPAD: returns a string padded with a specified character on the left or right
LTRIM, RTRIM: returns a string with leading or trailing characters removed
DECODE: returns a decoded value based on a set of conditions
SOUNDEX: returns the soundex code of a string

Conversion Functions

TO-NUMBER: converts a string to a number
TO-CHAR: converts a number to a string

Joins and Unions

Joins: combine data from multiple tables based on a common column
Unions: combine the results of multiple queries into a single result set

Description

Test your knowledge on SQL components such as data types, operators, DDL and DML commands like CREATE, ALTER, DROP, SELECT, INSERT, DELETE, UPDATE, order by clause, Group by, Having clause, views, and various functions including Numeric, Group, Character, and Conversion functions.