Brainscape's Knowledge GenomeTM

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


See full index

SQL classes > SQL terms > Flashcards

SQL terms Flashcards

1
Q

GROUP BY

A

“How many times does the name of employees (N of times) appear on the table”
- results can be group according to specific field or fields (columns)
- if you only use GROUP BY clause you will only get distinct values for the column. like DISTINCT keyword (time efficency)
- in most cases, you will use use an aggregate function like COUNT(), SUM(),AVG(), MIN(), or MAX(), with an GROUP BY clause
- if you use COUNT() function, with GROUP BY clause, you will get a group of results of how many times each value appears on the table ( how time a names appears)
- Also, best practice, always include the field (“column”) you have grouped your results by in the SELECT statement. (“first_name”)
- placed after the WHERE clause
- placed before the ORDER BY clause
Syntax
- SELECT column_name(s) FROM table_name
WHERE conditions
GROUP BY column_name(s)
ORDER BY column_name(s);nswers this ty

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

COUNT() function -

A
  • The COUNT() function returns the number of rows in a database table.
  • this is a different than DISTINCT statement
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

The Asterisk * Symbol

A
  • the wildcard asterisk symbol (*) represents the names of all the columns (field) in the table.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

PRIMARY KEY

A

*Each table in sql has one unique primary key

  • all the values on this column are unique, they cant be NULL VALUES

*a table can only have one primary key

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

Table

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

What is Database?

A
  • database is a container that holds tables and other SQL structures realted to those tables.
  • A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Definiton Query

A

you retrive, fetch data from database

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

Columns

A

defintion - is a piece of data stored by your table
- - columns also known as fields
- we read columns from top to bottom record

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

What is ROW

A
  • definiton is a single set of columns that describe attributes of single thing.
  • we also use record and row intercheangebily
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

WHERE vs HAVING

A
  1. when using aggragate functions use GROUP BY and HAVING
  2. general condition use - WHERE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

interview question

What does GROUP BY do?

my personal definition

A
  • the GROUP BY clause groups all the row values on specific column of a table that appear more than once into one group. for example. how many times does argentina appear on the country column of the able. By using the aggregate function COUNT() with GROUP BY, we can fetch a column on the workpace that shows the number of times argentina appears on the country column
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Having Clause

A
  1. the HAVING clause was added because we cant use WHERE with aggregate functions
  2. frequently use with GROUP BY clause
  3. HAVING is like WHERE but applied to GROUP BY
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

WHERE clause

A
  1. Allows us to set conditions that refer to individual rows
  2. These conditon are applied before re-organizing the output into groups (using GROUP BY)
  3. use with general conditions
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is Primary Key

A

*Each table in sql has one unique primary key
* all the values on this column are unique, they can’t be NULL VALUES

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

What does SQL stand for?

A
  • Structured Query Language
  • It is the standard language for relational database management systems.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is Database?

A
  • A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system.
  • a database is collection of objects
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What Are SQL constraints

A

specific rules, or limits, that we define in our table

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

What is a foreign key?

A
  1. points to the column of another table, thus it links two tables.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Relational Database

A

is a database where tables are related

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

Data Definition Language (DDL)

A
  • group of SQL statements that you can execute to manage database objects. Such as tables, views and more.
  • Sample Statements:
    1. create, alter, drop, name, truncate
  • these set of statements allow users to define or modify data structures and objects. Exp. Tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Unique Constraint (unique Key)

A
  • used whenever you would like to specify that you don’t want to see duplicate data in a given column
  • The UNIQUE constraint ensures that all values in a column are different, but you can have NULL values

*Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

*A PRIMARY KEY constraint automatically has a UNIQUE constraint.

*you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

22
Q

Default Constraint

A
  • helps us assign a particular default value to every column in a table
  • The default value will be added to all new records, if no other value is specified.
23
Q

What is NOT NULL CONTRAINT

A
  • By default, a column can hold NULL values. (missing values)
  • The Null Constraint enforces a column TO NOT ACCEPT NULL values
24
Q

What is Null?

A
  • a field with NULL VALUE is a field with no value
  • How do you test for NULL values
    *empty values
  • missing data, missin value
  • dont confuse a null value with a value of zero or with “none” response
25
Q

SELECT Statement

A
  • allows you to extract a fraction of the entire dataset
  • used to retrieve data from database objects, like table
    *used to “query data from database”
    Syntax: SELECT column_1, column_2,… column_n
    FROM table_name
  • we can select all of the columns using the wilrdcard “*”
26
Q

WHERE clause

A
  • It will allows us to set a condition upon which we will specify what part of the data we want to retrieve from the database.

Syntax:
SELECT column_1, column_2,… column_n
FROM table_name
WHERE condition;

27
Q

What is SQL Server?

A
  • SQL Server is a relational database system created by Microsoft.
  • It’s one of the top relational
    database systems used in the real-world
28
Q

EQUAL operators and WHERE CLAUSE

A
  • in sql, there are many other linking keywords and symbols, called operators, that you can use with the WHERE clause
  • Examples: AND, OR, IN, LIKE, BETWEEN, NOT IN, and ETC.
29
Q

What is a user defined database?

A

a user defined database is a database you create. Most relational database systems have systems database.

30
Q

Data Manipulation Language (DML)

A
  • its statements allows us to manipulate the data in the tables of a database
  • Sample Statements
  • SELECT… FROM…
  • INSERT INTO… VALUES…
  • UPDATE… SET… WHERE…
  • DELETE FROM… WHERE…
31
Q

Data Contral Language (DCL)

A
  • DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.
32
Q

Transactional Control Language (TCL)

A
33
Q

UPDATE statement

A
  • used to update the values of *existing records in a table *
  • Syntax
  • UPDATE table_name
  • SET column 1 = value1, column2 = value2…
    **WHERE conditions **
  • if you dont provide a WHERE condition clause, all of the rows of the table will be updated
34
Q

COPYING TABLES

A

Syntax:
CREATE TABLE new_table_name AS
SELECT column1, column2,…
FROM existing_table_name
WHERE ….;

34
Q

ON DELETE CASCADE

A

if a specific value from the parent’s table primary key has been deleted, all there records from the child table referring to this value will be removed as well.

35
Q

Delete Statement

A
  1. The DELETE statement is used to delete existing records in a table with required conditions
  2. Syntax
    DELETE FROM table_name WHERE condition;
36
Q

Drop Statement

A
  • you wont be able to roll back to its or to the last COMMI statement
  • only use DROP table if you know you not going to use the table anymore
37
Q

Truncate Statement

A
  • The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.
  • auto-increment values will reset
38
Q

What is an Aggregate Function

A
  • the aggregate functions gather data from many rows of a table, then aggregate( cluster) into a single value
  • aggregate functions are also known as summarizinf functions
  • they exist because stakeholder want summarized data not detailed data
39
Q

COUNT function

A
  1. this functions applicable to both numeric and non-numeric data
  2. COUNT( DISTINCT) - helps us find the number of unique values in a column
  3. this function ignores NULL values
  4. the COUNT(*) returns the number of rows of a table and including NULL VALUES
40
Q

SUM function

A
  • only works with numberic values, we cant use SUM(*) SYNTAX
41
Q

ROUND funcition

A

use as ROUND(#,decimal_places)
- we can use numeric values, or math or
- other functons when they return a single value

42
Q

COALESCE function

A
  • COALESCE function can have one, two or more argument
  • The COALESCE() function returns the first non-null value in a list.
  • COALESCE(val1, val2, …., val_n)
43
Q

How Do You Test For Null Value?

A
  • we can’t use the operators =,!=, <>, or < to test for NULL values
  • We use the keywords IS NULL and IS NOT NULL
  • Syntax:

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

44
Q

IFNULL function

A

-this functions returns a speciifc provided value if the table has null value
- it can pass two parameters

45
Q

SQL JOIN clause

A

-the clause is used to combine rows from two or more tables, based on a related column between them.
- a sql tool that allows us to contruct a relationship between objects
- we must find a related column from the two tables that contains the same data type
- the tables need not be logically adjcent

46
Q

Relational Schema

A
  • are the perfect tool that will help you find a strategy for linking tables
47
Q

ALTER TABLE

A

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name data_type;

48
Q

BETWEEN keyword

A
  • this keyword is used to filter data within specified range
  • Syntax:
    SELECT columns
    FROM table_name
    WHERE column_name** BETWEEN** value1 AND value2;
49
Q
A

SQL classes (1 decks)

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