SQL

Question 1

What is SQL and where is it used?

Answer 1

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.

Question 2

What are the different dialects of SQL

Answer 2

MS SQL Server using T-SQL,
Oracle using PL/SQL,
MS Access version of SQL is called JET SQL (native format) etc

Question 3

What are the functionalities of SQL?

Answer 3

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.

Question 4

What commands make up DDL

Answer 4

CREATE
ALTER
DROP

Question 5

What commands make up DML?

Answer 5

SELECT
INSERT
UPDATE
DELETE

Question 6

What commands make up DCL

Answer 6

GRANT

REVOKE

Question 7

Syntax to create a database and then check for it in the list of databases

Answer 7

CREATE DATABASE DatabaseName;

SQL> SHOW DATABASES;

Question 8

Syntax to delete database

Answer 8

DROP DATABASE DatabaseName;

Question 9

Syntax to choose a certain database before beginning your operations

Answer 9

USE DatabaseName;

Question 10

Syntax to create a tabe

Answer 10

CREATETABLEPersons (PersonID int,
LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255)
);

Question 11

Give a list of the MYSQL data types

Answer 11

Varchar
Tinytext
Blob
Int
Tinyint
Bigint
Double
Date
Datetime
Timestamp

Question 12

Syntax to insert values into the database

Answer 12

INSERT INTO TABLE_NAME (column1,column2,column3,…columN)

VALUES (value1,value2,value3,…valueN);

Question 13

Syntax to populate one table using another table

Answer 13

INSERT INTO first_table[(column1,column2,…columnN)]
SELECT column1,column2,…,columnN
FROM second_table
[WHERE condition];

Question 14

Syntax for the select statement

Answer 14

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;

Question 15

Syntax of the select ..where statement with both one and many conditions

Answer 15

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

Question 16

Syntax of the UPDATE Query

Answer 16

UPDATE table_name
SET column1=value1,column2=value2…columnN=valueN
WHERE [condition];

Question 17

Syntax for delete query

Answer 17

DELETE FROM CUSTOMERS WHERE ID = 6;

Question 18

How is the like clause used?

Answer 18

WHERE Name LIKE ‘a%’

Question 19

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)

Answer 19

‘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

Question 20

Describe the SQL ORDER BY Clause

Answer 20

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.

Question 21

Syntax for the order by clause in both ascending and descending order

Answer 21

*ascending
SELECT * FROM CUSTOMERS
ORDER BY NAME, SALARY;

*descending
SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;

Question 22

Describe the SQLGROUP BYclause

Answer 22

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.

Question 23

Syntax for group by clause

Answer 23

SELECT column1, column2
FROM table_name
WHERE [conditions]
GROUP BY column1, column2

Question 24

Describe the SQL DISTINCT keyword and give it’s syntax

Answer 24

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;

Question 25

What functionalities do views give to the user

Answer 25

• 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.

Question 26

What is the syntax for creating a view

Answer 26

CREATE VIEW view_name AS
SELECT column 1,column2…
FROM table_name

Question 27

Do exercise in slide 33

Answer 27

dfkv

Question 28

Describe SQL null values

Answer 28

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

Question 29

Give the syntax for the IS NULL and IS NOT NULL statements

Answer 29

*is null
SELECT column
FROM table
WHERE column IS NULL

*is not null
SELECT column
FROM table
WHERE column IS NOT NULL

Question 30

Describe the MIN() and MAX() functions and give their syntax

Answer 30

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;

Question 31

Describe the SQL COUNT() function and give its syntax

Answer 31

The COUNT() function returns the number of rows that matches a specified criteria.
COUNT() Syntax
SELECTCOUNT(column_name)
FROMtable_name
WHEREcondition;

Question 32

Describe the SQL AVG() and SUM() functions and give their syntax

Answer 32

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;

Question 33

Describe the SQL BETWEEN operator and give its syntax

Answer 33

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;

Question 34

Do the exercise in https://www.codecademy.com/learn/learn-sql

Answer 34

sdn

Question 35

What is the syntax of making primary and foreign keys

Answer 35

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);