SQL Flashcards
What is SQL and where is it used?
SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.
SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.
What are the different dialects of SQL
MS SQL Server using T-SQL,
Oracle using PL/SQL,
MS Access version of SQL is called JET SQL (native format) etc
What are the functionalities of SQL?
Allows users to access data in the relational database management systems.
Allows users to describe the data.
Allows users to define the data in a database and manipulate that data.
Allows to embed within other languages using SQL modules, libraries & pre-compilers.
Allows users to create and drop databases and tables.
Allows users to create view, stored procedure, functions in a database.
Allows users to set permissions on tables, procedures and views.
What commands make up DDL
CREATE
ALTER
DROP
What commands make up DML?
SELECT
INSERT
UPDATE
DELETE
What commands make up DCL
GRANT
REVOKE
Syntax to create a database and then check for it in the list of databases
CREATE DATABASE DatabaseName;
SQL> SHOW DATABASES;
Syntax to delete database
DROP DATABASE DatabaseName;
Syntax to choose a certain database before beginning your operations
USE DatabaseName;
Syntax to create a tabe
CREATETABLEPersons (PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
Give a list of the MYSQL data types
Varchar Tinytext Blob Int Tinyint Bigint Double Date Datetime Timestamp
Syntax to insert values into the database
INSERT INTO TABLE_NAME (column1,column2,column3,…columN)
VALUES (value1,value2,value3,…valueN);
Syntax to populate one table using another table
INSERT INTO first_table[(column1,column2,…columnN)]
SELECT column1,column2,…,columnN
FROM second_table
[WHERE condition];
Syntax for the select statement
SELECT column1, column2, columnN FROM table_name;
If you want to fetch all the fields available in the field, then you can use the following syntax.
SELECT * FROM table_name;
Syntax of the select ..where statement with both one and many conditions
SELECT ID, NAME, SALARY
FROM CUSTOMERS
WHERE SALARY > 2000;
*Many conditions
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND[condition2]…AND [conditionN];
*Instead of and one may also use OR
Syntax of the UPDATE Query
UPDATE table_name
SET column1=value1,column2=value2…columnN=valueN
WHERE [condition];
Syntax for delete query
DELETE FROM CUSTOMERS WHERE ID = 6;
How is the like clause used?
WHERE Name LIKE ‘a%’
What are the different ways of using the wild cards in conjunction with the LIKE operator
(begins with, ends with, in a particular position, in any position, specified number of characters, combination)
‘a%’- start with a
‘%a’- end with a
‘%or%’- has the the letters or in any position
‘r%’- has r in second position
‘a%_%’ starts with a and has 3 characters in length
‘a%o’- starts with a and ends with o
Describe the SQL ORDER BY Clause
The SQLORDER BYclause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.
Syntax for the order by clause in both ascending and descending order
*ascending
SELECT * FROM CUSTOMERS
ORDER BY NAME, SALARY;
*descending
SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;
Describe the SQLGROUP BYclause
The SQLGROUP BYclause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
Syntax for group by clause
SELECT column1, column2
FROM table_name
WHERE [conditions]
GROUP BY column1, column2
Describe the SQL DISTINCT keyword and give it’s syntax
The SQLDISTINCTkeyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.
E.g.
SELECT DISTINCT SALARY FROM CUSTOMERS
ORDER BY SALARY;
What functionalities do views give to the user
- Structure data in a way that users or classes of users find natural or intuitive.
- Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
- Summarize data from various tables which can be used to generate reports.
What is the syntax for creating a view
CREATE VIEW view_name AS
SELECT column 1,column2…
FROM table_name
Do exercise in slide 33
dfkv
Describe SQL null values
A field with a NULL value is a field with unknown value
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, .
We will have to use the IS NULL and IS NOT NULL operators instead.
IS NULL Syntax
Give the syntax for the IS NULL and IS NOT NULL statements
*is null
SELECT column
FROM table
WHERE column IS NULL
*is not null
SELECT column
FROM table
WHERE column IS NOT NULL
Describe the MIN() and MAX() functions and give their syntax
The MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.
MIN() Syntax
SELECT MIN(column_name)
FROM table
WHERE condition;
MAX() Syntax
SELECT MAX(column_name)
FROM table
WHERE condition;
Describe the SQL COUNT() function and give its syntax
The COUNT() function returns the number of rows that matches a specified criteria. COUNT() Syntax SELECTCOUNT(column_name) FROMtable_name WHEREcondition;
Describe the SQL AVG() and SUM() functions and give their syntax
The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column. Syntax SELECTAVG(column_name) FROMtable_name WHEREcondition; SELECTSUM(column_name) FROMtable_name WHEREcondition;
Describe the SQL BETWEEN operator and give its syntax
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
Example
SELECT*FROMProducts
WHEREPriceBETWEEN10AND20;
Do the exercise in https://www.codecademy.com/learn/learn-sql
sdn
What is the syntax of making primary and foreign keys
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
