SQL COMMANDS Flashcards
(93 cards)
1
Q
DDL
A
DATA DEFINITION LANGUAGE
These SQL commands are used for creating, modifying, and dropping the structure of database objects
1
Q
SQL
A
STRUCTURED QUERY LANGUAGE
2
Q
CREATE
A
It creates a new table, a view of a table.
DDL
3
Q
ALTER
A
It modifies the existing table.
DDL
4
Q
TRUNCATE
A
5
Q
DROP
A
It deletes the entire table or other objects in the database.
DDL
6
Q
RENAME
A
DDL
7
Q
DML
A
DATA MANIPULATION LANGUAGE
These SQL commands are used for storing, retrieving, modifying, and deleting data.
8
Q
SELECT
A
It extracts certain records from one or more table
DML COMMAND
9
Q
INSERT
A
It creates a record in the existing table.
DML COMMAND
10
Q
UPDATE
A
It modifies the existing record of the table.
DML COMMAND
11
Q
DELETE
A
It deletes the records in the table and even delete the complete table.
DML COMMAND
12
Q
TCL
A
TRANSACTION CONTROL LANGUAGE
These SQL commands are used for managing changes affecting the data
13
Q
COMMIT
A
14
Q
ROLLBACK
A
15
Q
SAVEPOINT
A
16
Q
DCL
A
DATA CONTROL LANGUAGE
These SQL commands are used for providing security to database objects.
17
Q
WHERE CLAUSE
A
The condition you provide in the WHERE clause filters the rows retrieved from the table and gives you only those rows which you expected to see. WHERE clause can be used along with SELECT, DELETE, UPDATE statements.
18
Q
SELECT COMMAND
A
SQL SELECT statement is used to query or retrieve data from a table in the database
19
Q
SQL OPERATOR TYPES
A
COMPARISON AND LOGICAL
20
Q
COMPARISON OPERATORS
SYMBOLS
A
= EQUAL TO
<>, != NOT EQUAL TO
< LESS THAN
> GREATER THAN
>= GREATER THAN OR EQUAL TO
<= LESS THAN OR EQUAL TO
21
Q
LOGICAL OPERATORS
A
AND
OR
NOT
22
Q
SQL OPERATORS USED WITH WHAT CLAUSES
A
MAINLY:
WHERE AND HAVING
23
Q
GROUP BY CLAUSE
A
The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns.
24
SQL WILDCARDS
_
%
25
%
SUBSTITUTE FOR ZERO OR MORE CHARACTERS
26
_
SUBSTITUTE FOR A SINGLE CHARACTER
27
ALTER TABLE
ADD COLUMN
ALTER TABLE table_name ADD column_name datatype;
==
ALTER TABLE employee ADD experience number(3);
28
ALTER TABLE DROP COLUMN
ALTER TABLE table_name DROP column_name;
==
ALTER TABLE employee DROP location;
29
ALTER TABLE MODIFY COLUMN
ALTER TABLE table_name MODIFY column_name datatype;
==
ALTER TABLE employee MODIFY salary number(15,2);
30
RENAME COMMAND
RENAME old_table_name To new_table_name;
==
RENAME employee TO my_emloyee;
31
STRING DATA TYPES
CHAR(X)
VAR(CHARX)
TEXT
32
TEXT DATA TYPE
This type is used to store long textual information.
33
VARCHAR(X) DATA TYPE
This type got its name from Varying Characters. This data type doesn’t pad unnecessary space. Here x is the number of characters to store
34
CHAR(X)
This data type is space padded to fill the number of characters specified. Here x is the number of characters to store.
35
DATE TIME DATA TYPES
DATE
TIME
36
DATE
This data type is used to store only date information.
37
TIME
This data type is used to store only time specific information.
38
NUMERIC DATA TYPES
INT
39
NULL DATA TYPE
LEFT BLANK
WHERE _____ IS NULL
WHERE ____ IS NOT NULL
40
SQL GROUP FUNCTIONS
COUNT
MAX
MIN
AVG
SUM
DISTINCT
41
SQL DISTINCT
This function is used to select the distinct rows.
For Example: If you want to select all distinct department names from employee table, the query would be:
SELECT DISTINCT dept FROM employee;
To get the count of employees with unique name, the query would be:
SELECT COUNT (DISTINCT name) FROM employee;
42
SQL MAX()
This function is used to get the maximum value from a column.
To get the maximum salary drawn by an employee, the query would be:
SELECT MAX (salary) FROM employee;
43
SQL MIN()
SQL MIN(): This function is used to get the minimum value from a column.
To get the minimum salary drawn by an employee, he query would be:
SELECT MIN (salary) FROM employee;
44
SQL AVG()
This function is used to get the average value of a numeric column.
To get the average salary, the query would be
SELECT AVG (salary) FROM employee;
45
SQL SUM()
This function is used to get the sum of a numeric column
To get the total salary given out to the employees,
SELECT SUM (salary) FROM employee;
46
SQL ORDER BY
ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. Oracle sorts query results in ascending order by default.
47
ORDER BY ASC
ASCENDING
1-9
A-Z
SELECT name, salary FROM employee ORDER BY name, salary;
48
ORDER BY DESC
DESCENDING
100-1
Z-A
MAX - MIN
SELECT name, salary
FROM employee
ORDER BY name, salary DESC;
49
COMPARISON OPERATOR KEYWORDS
LIKE
IN
BETWEEN...AND
IS NULL
50
COMPARISON OPERATOR:
LIKE
The LIKE operator is used to list all rows in a table whose column values match a specified pattern. It is useful when you want to search rows to match a specific pattern, or when you do not know the entire value. For this purpose we use a wildcard character '%'.
For example: To select all the students whose name begins with 'S'
SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE 'S%';
SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE '_a%';
51
COMPARISON OPERATOR:
IN
The IN operator is used when you want to compare a column with more than one value. It is similar to an OR condition.
For example: If you want to find the names of students who are studying either Maths or Science, the query would be like,
SELECT first_name, last_name, subject
FROM student_details
WHERE subject IN ('Maths', 'Science');
52
COMPARISON OPERATOR:
BETWEEN...AND
The operator BETWEEN and AND, are used to compare data for a range of values.
For Example: to find the names of the students between age 10 to 15 years, the query would be like,
SELECT first_name, last_name, age
FROM student_details
WHERE age BETWEEN 10 AND 15;
53
COMPARISON OPERATOR:
IS NULL
A column value is NULL if it does not exist. The IS NULL operator is used to display all the rows for columns that do not have a value.
For Example: If you want to find the names of students who do not participate in any games, the query would be as given below
SELECT first_name, last_name
FROM student_details
WHERE games IS NULL
54
INTEGER
WHOLE NUMBER
55
REAL
FLOATING POINT
56
COUNT()
Returns the number of rows that match the specified criteria.
57
AGGREGATE FUNCTIONS
In SQL, aggregate functions perform a calculation on a set of values and return a single value. They are often used with the GROUP BY clause of the SELECT statement.
58
PRIMARY KEYS
Primary keys are special columns that are used to uniquely identify each row of a table in SQL.
59
FOREIGN KEY
When the primary key for one table appears in a different table, it is called a foreign key. The most common types of joins will be joining a foreign key from one table with the primary key from another table.
60
COMPOSITE KEY
Sometimes, having one primary key per table is not enough to uniquely identify a row. In such cases, multiple columns would work as composite keys for the table. This requirement should be detected during the designing phase of a database.
61
SQL ARITHMETIC OPERATORS
+: Addition
-: Subtraction
*: Multiplication
/: Division
%: Modulo (remainder)
62
EXISTS
Tests a subquery and returns TRUE if at least one record satisfies it.
63
BETWEEN
Selects values, inclusively of beginning and end values, within a given range. BETWEEN works with numbers, text, or date data types.
64
IN
Allows the user to specify multiple values in the WHERE clause.
65
IS NOT NULL
Checks if a value is not NULL.
66
IS NULL
Checks if a value is NULL.
67
LIKE
Returns `TRUE` if its first text argument matches the wildcard pattern in its second argument.
68
NOT
Queries for items in an expression that return NOT TRUE for some condition(s).
69
OR
Test if any condition in a given expression evaluates to TRUE.
70
AND
Tests if all conditions in a given expression evaluate to TRUE.
71
SINGLE LINE
COMMENTS
-- COMMENT HERE
72
MULTI LINE COMMENTS
Multi-line comments start with /* and end with */.
73
CONSTRAINT
Constraints in SQL are the rules applied to the values of individual columns. They add information about how a column can be used after specifying the data type for a column. They can be used to tell the database to reject inserted data that does not adhere to a certain restriction.
74
CONSTRAINT EXAMPLES
PRIMARY KEY
UNIQUE
NOT NULL
DEFAULT
75
UNIQUE
UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns.
76
DEFAULT
DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.
77
Usage Funnel Stages
Awareness: Potential customers become aware of the product or service.
Interest: They show interest by seeking more information.
Evaluation: They evaluate the product against their needs and other offerings.
Decision: They decide to proceed with a purchase or subscription.
Action: They complete the purchase or sign up, becoming a customer or user.
Retention: After becoming customers, the focus shifts to retaining them, which can also be seen as part of the extended funnel.
78
SQL Analyzing User Churn
//
Churn rate
///
attrition rate
Churn rate, also known as attrition rate, is a critical metric in business analytics that measures the rate at which customers discontinue their relationship with a service or subscription within a certain period. It is a significant indicator of customer satisfaction, loyalty, and the product's or service's overall value proposition.
79
Calculate Churn rate
To calculate the churn rate, you typically divide the number of customers who have left the service during a specific time period by the total number of customers at the beginning of that period. The result is usually expressed as a percentage. Here's a basic formula for churn rate calculation:
Churn Rate
=
(Number of Customers Lost During the Period/
Total Number of Customers at the Start of the Period)
×
100
80
Usage Funnel Concept
concept used in marketing and product management to visualize the journey a customer or user takes towards completing a specific goal or action within a product, service, or website. The funnel metaphor is used because at each step of the process, a portion of users typically drop off, narrowing down the group that moves on to the next step, much like the shape of a funnel.
81
Web Traffic Attribution
82
UTM Parameters
83
RDBMS
Relational Database Management System
84
What is a Relational Database Management System?
A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database.
85
Popular RDBMS
MySQL
PostgreSQL
Oracle DB
SQL Server
SQLite
86
MySQL
MySQL is the most popular open source SQL database. It is typically used for web application development, and often accessed using PHP.
The main advantages of MySQL are that it is easy to use, inexpensive, reliable (has been around since 1995), and has a large community of developers who can help answer questions.
Some of the disadvantages are that it has been known to suffer from poor performance when scaling, open source development has lagged since Oracle has taken control of MySQL, and it does not include some advanced features that developers may be used to.
87
PostgreSQL
PostgreSQL is an open source SQL database that is not controlled by any corporation. It is typically used for web application development.
PostgreSQL shares many of the same advantages of MySQL. It is easy to use, inexpensive, reliable and has a large community of developers. It also provides some additional features such as foreign key support without requiring complex configuration.
The main disadvantage of PostgreSQL is that it can be slower in performance than other databases such as MySQL. It is also slightly less popular than MySQL.
88
Oracle DB
Oracle Corporation owns Oracle Database, and the code is not open sourced.
Oracle DB is for large applications, particularly in the banking industry. Most of the world’s top banks run Oracle applications because Oracle offers a powerful combination of technology and comprehensive, pre-integrated business applications, including essential functionality built specifically for banks.
The main disadvantage of using Oracle is that it is not free to use like its open source competitors and can be quite expensive.
89
SQLite
SQLite is a popular open source SQL database. It can store an entire database in a single file. One of the most significant advantages this provides is that all of the data can be stored locally without having to connect your database to a server.
SQLite is a popular choice for databases in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets. The SQL courses on Codecademy use SQLite.
90
SQL Server
Microsoft owns SQL Server. Like Oracle DB, the code is close sourced.
Large enterprise applications mostly use SQL Server.
Microsoft offers a free entry-level version called Express but can become very expensive as you scale your application.
91
SQL File Extensions
(On Codecademy)
.sqlite
.sql
92
