SQL Flashcards
An organized collection of data or information
Database
A set of computer programs for organizing the information in a database
Database Management System
A DBMS provides tools for data input, storage, retrieval and manipulation
x
Where are Database applications
everywhere from Banking, online ticket booking, online retailer, etc
What does DBMS do?
DBMS controls all functions of capturing, processing, storing, retrieving data and generates various forms of data output
Data is organized in
tables
Table contains
rows and columns
Tables can be related to one another through common values called
Keys
Uniquely identifies each row of the table
Primary Key
Column in one table that serves as a primary key in another
Foreign Key
What are different types of relationship exist
One to One, One to Many, Many to Many
A set of rules to help designers to reduce redundancy and avoid data update anomalies
Normalization
Entity and Attributes
X
Language for accessing and manipulating the data
DML
Language for Create, Alter, Drop, Truncate, Comment and Rename
DDL
DDL stands for
Data Definition Language
Which language is used for the control of data
Data Control Language DCL
What operations can you perform using DCL
Grant and Revoke
What are transaction control statements
Commit, Rollback, Savepoint
Restore database to original from the last COMMIT
Rollback
Save work done
Commit
Data Types in SQL
Character, Varchar, Boolean, Integer, Float, Date, Time, Timestamp
Fixed width character string
Char
Variable width character string
Varchar
Whole numbers
int
Floating precision number
float
Syntax for creating a table
Create Table table_name (column1 data_type (size), column2 data_type (size));
What are the different types of Operators in SQL
Arithmetic Operator, Relational Operator, Logical Operator and Comparison Operator
+ - * / are
Arithmetic Operators
=, != and <>, >, =, <=, !
Relational Operators
AND OR NOT
Logical Operators
IN BETWEEN LIKE
Comparison Operators
Used to temporarily rename a table or a column heading. to make them more readable
Alias
Where Clause
It’s a condition to select particular rows or data from the table
Delete command
Used to delete the rows from the table
This command removes all rows from the table
Truncate
This command removes table from the database
Drop
Drop Command
Drop Table
Delete Command
Delete Table where
Truncate Command
Truncate Table
Drop and Truncate are
DDL commands
Delete is a
DML command
SQL INSERT INTO syntax
INSERT INTO table_name (column1, column2, colum3) Values (value1, value2, value3);
Create Table Statement
Used to create table in a database
Create Table Syntax
Create Table (column1 data type (size), column2 data type (size), column3 data type (size));
Instructions used to communicate with the database to perform specific task that with data
SQL commands
Create Alter and Drop; Grant and Revoke
DDL commands
Select, Insert, Update, and Delete
DML commands
These SQL commands are used for storing, retrieving, modifying and deleting data
DML
A clause used to combine records from two or more tables in a database
JOINS
What are the different types of Join available in SQL
Inner Join, Left Join, Right Join, Full Join, Self Join, Cartesian Join
What’s the most frequently used Join statement
Inner Join
Inner Join creates a new result table by combining column values of two tables
table 1 and table 2
Basic Syntax of Inner Join
Select table1.column1, table2.column2 from table1 inner join table2 ON table1.common_filed = table2.commond_field
Left Join returns all rows from the left table even if there are no matches in the right table
x
Basic Syntax for Left Join
Select column1, column2, column3 from table 1 left join table 2 On table1.common_field = table2.common_field
Right Join returns all the rows from the right table even if there are no matches in the left table
x
Basic Syntax for Right Join
Select column1, column2, column3 from table1 right join table 2 on table1.common_field = table2.common_field
