Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL > SQL COMMANDS > Flashcards

SQL COMMANDS Flashcards

1
Q

DDL

A

DATA DEFINITION LANGUAGE
These SQL commands are used for creating, modifying, and dropping the structure of database objects

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
1
Q

SQL

A

STRUCTURED QUERY LANGUAGE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

CREATE

A

It creates a new table, a view of a table.
DDL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

ALTER

A

It modifies the existing table.
DDL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

TRUNCATE

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

DROP

A

It deletes the entire table or other objects in the database.
DDL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

RENAME

A

DDL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

DML

A

DATA MANIPULATION LANGUAGE
These SQL commands are used for storing, retrieving, modifying, and deleting data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

SELECT

A

It extracts certain records from one or more table
DML COMMAND

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

INSERT

A

It creates a record in the existing table.
DML COMMAND

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

UPDATE

A

It modifies the existing record of the table.
DML COMMAND

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

DELETE

A

It deletes the records in the table and even delete the complete table.
DML COMMAND

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

TCL

A

TRANSACTION CONTROL LANGUAGE
These SQL commands are used for managing changes affecting the data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

COMMIT

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

ROLLBACK

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

SAVEPOINT

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

DCL

A

DATA CONTROL LANGUAGE
These SQL commands are used for providing security to database objects.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

SELECT COMMAND

A

SQL SELECT statement is used to query or retrieve data from a table in the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

SQL OPERATOR TYPES

A

COMPARISON AND LOGICAL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

LOGICAL OPERATORS

A

AND
OR
NOT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

SQL OPERATORS USED WITH WHAT CLAUSES

A

MAINLY:
WHERE AND HAVING

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

SQL WILDCARDS

A

_
%

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

%

A

SUBSTITUTE FOR ZERO OR MORE CHARACTERS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

_

A

SUBSTITUTE FOR A SINGLE CHARACTER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

ALTER TABLE
ADD COLUMN

A

ALTER TABLE table_name ADD column_name datatype;

==

ALTER TABLE employee ADD experience number(3);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

ALTER TABLE DROP COLUMN

A

ALTER TABLE table_name DROP column_name;

==

ALTER TABLE employee DROP location;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

ALTER TABLE MODIFY COLUMN

A

ALTER TABLE table_name MODIFY column_name datatype;

==

ALTER TABLE employee MODIFY salary number(15,2);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

RENAME COMMAND

A

RENAME old_table_name To new_table_name;

==

RENAME employee TO my_emloyee;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

STRING DATA TYPES

A

CHAR(X)
VAR(CHARX)
TEXT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

TEXT DATA TYPE

A

This type is used to store long textual information.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

VARCHAR(X) DATA TYPE

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

CHAR(X)

A

This data type is space padded to fill the number of characters specified. Here x is the number of characters to store.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

DATE TIME DATA TYPES

A

DATE
TIME

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

DATE

A

This data type is used to store only date information.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

TIME

A

This data type is used to store only time specific information.

38
Q

NUMERIC DATA TYPES

A

INT

39
Q

NULL DATA TYPE

A

LEFT BLANK
WHERE _____ IS NULL
WHERE ____ IS NOT NULL

40
Q

SQL GROUP FUNCTIONS

A

COUNT
MAX
MIN
AVG
SUM
DISTINCT

41
Q

SQL DISTINCT

A

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
Q

SQL MAX()

A

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
Q

SQL MIN()

A

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
Q

SQL AVG()

A

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
Q

SQL SUM()

A

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
Q

SQL ORDER BY

A

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
Q

ORDER BY ASC

A

ASCENDING
1-9
A-Z
SELECT name, salary FROM employee ORDER BY name, salary;

48
Q

ORDER BY DESC

A

DESCENDING
100-1
Z-A
MAX - MIN

SELECT name, salary
FROM employee
ORDER BY name, salary DESC;

49
Q

COMPARISON OPERATOR KEYWORDS

A

LIKE
IN
BETWEEN…AND
IS NULL

50
Q

COMPARISON OPERATOR:
LIKE

A

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
Q

COMPARISON OPERATOR:
IN

A

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
Q

COMPARISON OPERATOR:
BETWEEN…AND

A

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
Q

COMPARISON OPERATOR:
IS NULL

A

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
Q

INTEGER

A

WHOLE NUMBER

55
Q

REAL

A

FLOATING POINT

56
Q

COUNT()

A

Returns the number of rows that match the specified criteria.

57
Q

AGGREGATE FUNCTIONS

A

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
Q

PRIMARY KEYS

A

Primary keys are special columns that are used to uniquely identify each row of a table in SQL.

59
Q

FOREIGN KEY

A

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
Q

COMPOSITE KEY

A

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
Q

SQL ARITHMETIC OPERATORS

A

+: Addition
-: Subtraction
*: Multiplication
/: Division
%: Modulo (remainder)

62
Q

EXISTS

A

Tests a subquery and returns TRUE if at least one record satisfies it.

63
Q

BETWEEN

A

Selects values, inclusively of beginning and end values, within a given range. BETWEEN works with numbers, text, or date data types.

64
Q

IN

A

Allows the user to specify multiple values in the WHERE clause.

65
Q

IS NOT NULL

A

Checks if a value is not NULL.

66
Q

IS NULL

A

Checks if a value is NULL.

67
Q

LIKE

A

Returns TRUE if its first text argument matches the wildcard pattern in its second argument.

68
Q

NOT

A

Queries for items in an expression that return NOT TRUE for some condition(s).

69
Q

OR

A

Test if any condition in a given expression evaluates to TRUE.

70
Q

AND

A

Tests if all conditions in a given expression evaluate to TRUE.

71
Q

SINGLE LINE
COMMENTS

A

– COMMENT HERE

72
Q

MULTI LINE COMMENTS

A

Multi-line comments start with /* and end with */.

73
Q

CONSTRAINT

A

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
Q

CONSTRAINT EXAMPLES

A

PRIMARY KEY
UNIQUE
NOT NULL
DEFAULT

75
Q

UNIQUE

A

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
Q

DEFAULT

A

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
Q

Usage Funnel Stages

A

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
Q

SQL Analyzing User Churn
//
Churn rate
///
attrition rate

A

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
Q

Calculate Churn rate

A

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:

(Number of Customers Lost During the Period/
Total Number of Customers at the Start of the Period)
×
100

80
Q

Usage Funnel Concept

A

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
Q

Web Traffic Attribution

A
82
Q

UTM Parameters

A
83
Q

RDBMS

A

Relational Database Management System

84
Q

What is a Relational Database Management System?

A

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
Q

Popular RDBMS

A

MySQL
PostgreSQL
Oracle DB
SQL Server
SQLite

86
Q

MySQL

A

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
Q

PostgreSQL

A

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
Q

Oracle DB

A

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
Q

SQLite

A

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
Q

SQL Server

A

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
Q

SQL File Extensions
(On Codecademy)

A

.sqlite
.sql

92
Q
A

SQL (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions