Starting from SQL Flashcards
What is SQL?
It is a programming language with which we can create, maintain and request information from Databases.
What is a Database?
It is an organized collection of data or information.
what are relational databases?
A structured database containing tables that are related to each other.
Tables might be similar in one or more columns.
The columns that relate 2 tables to each other are called keys.
There are other kinds of databases as well.
What are 2 types of keys in relational databases?
Primary key - A column where every row has a unique key.
Foreign key - A column in a table that is a primary key in another tale.
Foreign keys unlike primary keys can have empty values or duplicates.
What is a Query?
A query is a request for data from a database table or a combination of tables.
How we can access SQLite version of SQL from Linux command line?
sqlite3
Give me an example of a scenario where we might Linux as compared to SQL.
Sometimes data format that we need to examine might not be compatible with SQL (for example - text files). For such cases, we need to use Linux.
What are the 2 basic keywords used in SQL queries?
SELECT - indicates which columns to return (* denotes to return all the columns of a table)
FROM - Indicates which tables to query
What keyword do we use to get an ordered result of a query to a database?
We can craft our query to order the query output based on any of the columns in the database.
SYNTAX - >
SELECT customer_ID, city
FROM customers
ORDER BY city;
In descending ->
ORDER BY city DESC;
Note - We can also sort based on multiple columns.
What are the basic filters in SQL queries?
WHERE -> acts as a clause that we use to create a filter
% -> Wildcard
- -> Wildcard
Like -> operator
Give me example of usage of WHERE in SQL.
WHERE country = ‘USA’
WHERE country LIKE “US_”
(In the above statement, underscore will substitute for only one another character )
WHERE name LIKE “man%”
(Above, percentage sign will substitute for any number of other characters)
WHERE NOT country = “USA”
What are the common datatypes in Databases?
Strings, Numbers, Data and Time
Note - Data and Time need to be enclosed within quotation marks like strings.
Give me an example of filtering use of comparison operator in SQL.
SELECT *
FROM employees
WHERE birthdate > ‘1970-01-01’;
What is inner join in SQL database tables?
It returns rows matching a specified column that exists in more than one table.
When we use INNER JOIN, all the columns in both tables are returned.
If the column exists in both of the tables, it is returned twice when SELECT * is used
What are different kinds of Joins in SQL?
Inner Join
Left Join
Right Join
Full join
Give me an example of Inner join syntax.
SELECT username, operating_system, employees.device_id
FROM employees
INNER JOIN machines ON employees.device_id = machines.device_id;
What are aggregate functions in SQL?
Count - returns a single number that represents the number of rows returned from your query.
AVG - for average
SUM - for sum
SELECT COUNT(firstname)
FROM customers
WHERE Country = ‘USA’;
How do we calculate risk to some asset?
Its formula is
Likelihood * impact = Risk
It depends on how often a risk can be exploited and what its impact on the organization if it is exploited.
What are the categories of a threat?
Intentional threat -
Unintentional threat -
Outsider threat -
Insider threat -
What are the 2 categories of vulnerabilities?
Technical vulnerability
Human vulnerability
What is Asset management?
It is the process of making an inventory of all the organization’s assets, tracking all the assets, and finding out all the associated risks to these assets.
It also includes asset classification.
What is asset classification?
Asset classification is the process of classifying assets based on how important an asset is to an organization and how sensitive it is.
How do we find out the sensitivity and importance of an asset to an organization?
To know the sensitivity and importance of an asset to an organization, we normally ask the following questions:
- What you have
- Where it is
- Who owns it
- How important it is
What are the common asset classifications that an asset can be categorized into?
- Restricted
- Confidential
- Internal only
- Public
What is information security?
Information Security is the practice of keeping information secured from unauthorized access in all its states :
Data-in-use
Data-at-rest
Data-in-transit
To protect data, we need to know what state our data is in.
What are the elements of a Security Plan?
Every Security Element has the following elements :
Policies - Policies are the set of rules that reduce risk and protect information.
Procedures -
Standards - these are the references based on what policies are created.
What are the components of the NIST Cyber security framework?
NIST CSF has 2 main components :
Core
Tiers
Profiles
What are the sub-components of the core component NIST CSF?
The core component has 5 sub-components :
- Identify
- Protect
- Detect
- Respond
- Recover
What is the use of Tier component of NIST CSF?
This provides security teams with a way to measure performance across each of the 5 functions of the core.
What is the use of Profile component of NIST CSF framework?
It is like a snapshot of the security posture of an organization at different times.
It is used to see the improvements in the security posture of an organization.
What are the different types of security controls?
Technical
Managerial / Administrative
Operational - controls like user training or incident response.
What is information privacy?
It is the practice of protecting information from unauthorized access and giving the right to choose.
What is the best way or best control to protect the data privacy?
By implementing the principle of least privilege.
What are service accounts?
Service accounts are assigned to applications that need to interact with the other software on the network.
What are the 3 common approaches to auditing user accounts?
We can audit user accounts using 3 ways:
- Usage audits
- Privilege audits - These audits happen on the accounts of a user who is with the organization for a long time and they tend to accumulate more privileges as they are promoted or change their department.
- Account change audits - in this one, security analysts look for unauthorized changes on user accounts.
What is Data governance?
It is a set of processes that defines how an organization manages information.
It defines policies that keep data secure throughout its lifecycle.
