Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
44 Cards in this Set
- Front
- Back
What is SQL?
|
SQL is the language to interact with database to insert, update, delete and write different constraints(condition) to the data in database.
|
|
Where do you write SQL query?
|
We write SQL queries using some these tools: Todd, Squirrel and Rapid SQL.
|
|
What is the most common syntax you have used while writing SQL query?
|
SELECT
|
|
What is Data?
|
Data is something which has some information like number, character or image
|
|
RDBMS
|
Relational Database Management System
|
|
DBA
|
Database admins
|
|
What is a View?
|
(alias for table) View is a virtual table which make the transaction very fast
-to make the queries faster we create a view |
|
What is DDL?
|
Data Definition Language (DDL)
The DDL part of SQL permits database tables to be created or deleted. |
|
What DML?
|
Data Manipulation Language (DML)
The query and update commands form the DML part of SQL -Select -Update -Insert Into -Delete |
|
SELECT
|
To select data from database
|
|
SELECT DISTINCT
|
To select only the unique values
|
|
WHERE Clause
|
To extract records that satisfy a specific condition
|
|
RDBMS
|
Relational Database System
-more than one table where the tables are related to each other. |
|
OODBMS
|
Object Oriented DB Management System
-store data in the form of object |
|
Object Relational Database
|
Combination of OODBMS and RDBMS
- complex data like audios, videos, ect.... |
|
SELECT
|
-to view data within the table.
SELECT customer, postcode FROM Addresses WHERE customer LIKE 'S%' |
|
UPDATE
|
–modifies records in the table.
UPDATE Addresses SET postcode=20143 WHERE city='Ellicott City' |
|
INSERT INTO
|
–inserts records into the table.
INSERT INTO Addresses VALUES ('C1001', 'Will', 'Ellicott City') INSERT INTO Addresses (Name, City) VALUES ('C1001', 'Will', 'Ellicott City') |
|
DELETE
|
–removes records from the table.
DELETE FROM Addresses WHERE CustomerID='C1001' DELETE FROM Addresses |
|
Primary Key
|
-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. |
|
BETWEEN
|
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' |
|
LIKE
|
LIKE operator is used to search for a specified pattern in a column
SELECT * FROM Addresses WHERE name LIKE 'Jo%' |
|
IN
|
IN operator allows you to specify multiple values in a WHERE clause
SELECT * FROM Address WHERE City IN ('Elliott City', 'Columbia') |
|
SQL Joins
|
SQL Joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.
|
|
INNER JOIN
|
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. |
|
LEFT JOIN
|
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) |
|
RIGHT JOIN
|
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) |
|
FULL JOIN
|
List all details from both the table
If we wanted to 'list all the customers and list all the order' |
|
Toad
|
-tool to connect to data base
-run SQL queries to read data from data base |
|
What are the two different ways we can run SQL queries?
|
-Edit the query
-Query builder |
|
AND
|
We use AND to ensure that two or more conditions hold true.
|
|
COUNT
|
-is a aggregate function it is normally used with GROUP BY
|
|
Data Migration project
|
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 |
|
What are the different sub languages in the SQL?
|
-DML
-DDL -DCL -TCL |
|
How to Create a table?
|
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 |
|
What are different Operators used in SQL?
|
>, < , = , !=, >= , < =
AND, OR, NOT LIKE, IN, BETWEEN.. AND |
|
What are some of the Aggregate Functions?
|
• AVG
• COUNT • MAX • MIN • SUM -Aggregate functions are usually used with GROUP BY statement |
|
The difference between UNION and INTERSECT?
|
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).
|
|
HAVING clause
|
-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. |
|
Example of HAVING clause.
|
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. |
|
Foreign Key
|
the primary key of another table
|
|
What Clauses have you used in SQL?
|
SELECT
FROM WHERE GROUP BY HAVING ORDER BY |
|
What is the difference between JOIN and UNION?
|
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. |
|
What is the difference among UNION, MINUS and INTERSECT?
|
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. |