Sql Basic

Front 1

What is SQL?

Back 1

SQL is the language to interact with database to insert, update, delete and write different constraints(condition) to the data in database.

Front 2

Where do you write SQL query?

Back 2

We write SQL queries using some these tools: Todd, Squirrel and Rapid SQL.

Front 3

What is the most common syntax you have used while writing SQL query?

Back 3

SELECT

Front 4

What is Data?

Back 4

Data is something which has some information like number, character or image

Front 5

RDBMS

Back 5

Relational Database Management System

Front 6

DBA

Back 6

Database admins

Front 7

What is a View?

Back 7

(alias for table) View is a virtual table which make the transaction very fast
-to make the queries faster we create a view

Front 8

What is DDL?

Back 8

Data Definition Language (DDL)
The DDL part of SQL permits database tables to be created or deleted.

Front 9

What DML?

Back 9

Data Manipulation Language (DML)
The query and update commands form the DML part of SQL
-Select
-Update
-Insert Into
-Delete

Front 10

SELECT

Back 10

To select data from database

Front 11

SELECT DISTINCT

Back 11

To select only the unique values

Front 12

WHERE Clause

Back 12

To extract records that satisfy a specific condition

Front 13

RDBMS

Back 13

Relational Database System
-more than one table where the tables are related to each other.

Front 14

OODBMS

Back 14

Object Oriented DB Management System
-store data in the form of object

Front 15

Object Relational Database

Back 15

Combination of OODBMS and RDBMS
- complex data like audios, videos, ect....

Front 16

SELECT

Back 16

-to view data within the table.
SELECT customer, postcode
FROM Addresses
WHERE customer LIKE 'S%'

Front 17

UPDATE

Back 17

–modifies records in the table.
UPDATE Addresses
SET postcode=20143
WHERE city='Ellicott City'

Front 18

INSERT INTO

Back 18

–inserts records into the table.

INSERT INTO Addresses
VALUES ('C1001', 'Will', 'Ellicott City')

INSERT INTO Addresses (Name, City)
VALUES ('C1001', 'Will', 'Ellicott City')

Front 19

DELETE

Back 19

–removes records from the table.

DELETE FROM Addresses
WHERE CustomerID='C1001'

DELETE FROM Addresses

Front 20

Primary Key

Back 20

-The PRIMARY KEY constraint uniquely identifies each record in a database table.
-Primary keys must contain unique values.
-A primary key column cannot contain NULL values.

Front 21

BETWEEN

Back 21

The BETWEEN operator is used in a WHERE clause to select a range of data between two values.

SELECT *
FROM Addresses
WHERE name BETWEEN 'Bill' AND 'Laura'

Front 22

LIKE

Back 22

LIKE operator is used to search for a specified pattern in a column

SELECT *
FROM Addresses
WHERE name LIKE 'Jo%'

Front 23

IN

Back 23

IN operator allows you to specify multiple values in a WHERE clause

SELECT * FROM Address WHERE City IN ('Elliott City', 'Columbia')

Front 24

SQL Joins

Back 24

SQL Joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

Front 25

INNER JOIN

Back 25

List Only the matched records from both tables

If we wanted to 'list customers who have orders'

It will not list any customers that do not have a order and it will not display any order that do not have a customer.

Front 26

LEFT JOIN

Back 26

Return all rows from the left table, and the matched rows from the right table

If we wanted to 'list all customers and their orders'
SELECT *
FROM customer LEFT JOIN orders on (P_ID=P_ID)

Front 27

RIGHT JOIN

Back 27

Return all rows from the right table, and the matched rows from the left table

If we wanted to 'list all orders and their customers'
SELECT *
FROM customer RIGHT JOIN orders on (P_ID=P_ID)

Front 28

FULL JOIN

Back 28

List all details from both the table

If we wanted to 'list all the customers and list all the order'

Front 29

Toad

Back 29

-tool to connect to data base
-run SQL queries to read data from data base

Front 30

What are the two different ways we can run SQL queries?

Back 30

-Edit the query
-Query builder

Front 31

AND

Back 31

We use AND to ensure that two or more conditions hold true.

Front 32

COUNT

Back 32

-is a aggregate function it is normally used with GROUP BY

Front 33

Data Migration project

Back 33

The project was to migrate the data from two different data bases to a new data base. My role as a tester was to:
-Data validation
To verify that all the date mearged
-Field validation
-to verify that the data moved from the source database to target database properly
-Columns from different tables merged without any issues

Front 34

What are the different sub languages in the SQL?

Back 34

-DML
-DDL
-DCL
-TCL

Front 35

How to Create a table?

Back 35

Syntax of CREATE Statement

Create table
(< Column 1> (Size),
(< Column 2> (Size),
(< Column 3> (Size),
(< Column n>
(Size));

Tablename should be unique
Tablename should start with a alphabet
Tablename cannot exceed 30 chars

Front 36

What are different Operators used in SQL?

Back 36

>, < , = , !=, >= , < =
AND, OR, NOT
LIKE, IN, BETWEEN.. AND

Front 37

What are some of the Aggregate Functions?

Back 37

• AVG
• COUNT
• MAX
• MIN
• SUM

-Aggregate functions are usually used with GROUP BY statement

Front 38

The difference between UNION and INTERSECT?

Back 38

The difference is that, while UNION essentially acts as an OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator (value is selected only if it appears in both statements).

Front 39

HAVING clause

Back 39

-The HAVING clause can be used with aggregate functions.
-It is usually used with GROUP BY
-When GROUP BY is not used, HAVING behaves almost like WHERE clause.

Front 40

Example of HAVING clause.

Back 40

Let's say you want a list of all the customers from Texas who made more than 5 orders last year.

- the Where clause to get all the orders from last year by people from Texas
- the Group By clause to group all the orders by customer (with a Count(OrderID) As OrderCount in your select clause)
- the Having clause would limit the customers listed to those having 5 or more orders.

Front 41

Foreign Key

Back 41

the primary key of another table

Front 42

What Clauses have you used in SQL?

Back 42

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

Front 43

What is the difference between JOIN and UNION?

Back 43

UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

By using JOINs, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL should use data from one table to select the rows in another table.

Front 44

What is the difference among UNION, MINUS and INTERSECT?

Back 44

UNION combines the results from 2 tables and eliminates duplicate records from the result set.

MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.

INTERSECT operator returns us only the matching or common rows between 2 result sets.