Infosys

hat is a View

A view is a virtual table which consists of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship.

What is normalization?

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.

What is Normalization?

Normalization represents the way of organizing structured data in the database efficiently. It includes creation of tables, establishing relationships between them, and defining rules for those relationships. Inconsistency and redundancy can be kept in check based on these rules, hence, adding flexibility to the database.

Transaction Isolation Levels

How much security you want to put into a transaction Higher security means the transactions are slower

Numeric Types

Numeric types consist of two-byte, four-byte, and eight-byte integers, four-byte and eight-byte floating-point numbers, and selectable-precision decimals. smallint, integer, bigint, decimal

How to create empty tables with the same structure as another table?

SELECT * INTO Students_copy FROM Students WHERE 1 = 2;

Only Unique full outer join

SELECT a, fruit_a, b, fruit_b FROM basket_a FULL JOIN basket_b ON fruit_a = fruit_b WHERE a IS NULL OR b IS NULL;

PostgreSQL full outer join

SELECT a, fruit_a, b, fruit_b FROM basket_a FULL OUTER JOIN basket_b ON fruit_a = fruit_b;

Left Join.

Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.

Cons

Not flexible, hard to make big changes bc of how thorough the documentation is

Second Normal Form (2NF)

Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.

Fourth Normal Form (4NF)

Meeting all the requirements of third normal form and it should not have multi- valued dependencies.

Constraints

Not Null: value in column can't be null or empty - Unique Key: ensures all values in column are unique => CAN ACCEPT NULL VALUE - Primary Key: uniquely identifies a record => combo of unique key and not null key - PK CAN BE MULTIPLE COLUMNS => CALLED A COMPOSITE KEY - Surrogate Key: column that is created only to hold the Primary Key - Foreign Key: reference to a primary key in another table (commonly associated with joins) - Orphan Record: a record in which the FK points to the PK that no longer exists (not a good thing to have)

What is a primary key?

A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.

What is a Stored Procedure?

A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary. The sole disadvantage of stored procedure is that it can be executed nowhere except in the database and occupies more memory in the database server. It also provides a sense of security and functionality as users who can't access the data directly can be granted access via stored procedures.

What is recursive stored procedure?

A stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.

An aggregate function

performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. Following are the widely used SQL aggregate functions: AVG() - Calculates the mean of a collection of values. COUNT() - Counts the total number of records in a specific table or view. MIN() - Calculates the minimum of a collection of values. MAX() - Calculates the maximum of a collection of values. SUM() - Calculates the sum of a collection of values. FIRST() - Fetches the first element in a collection of values. LAST() - Fetches the last element in a collection of values.

orphan record

when a child record with a foreign key points to a parent record of a primary key field that no longer exists

PostgreSQL left outer join

SELECT a, fruit_a, b, fruit_b FROM basket_a LEFT JOIN basket_b ON fruit_a = fruit_b WHERE b IS NULL; left join that returns rows from the left table that do not have matching rows from the right table:

PostgreSQL left join

SELECT a, fruit_a, b, fruit_b FROM basket_a LEFT JOIN basket_b ON fruit_a = fruit_b;

PostgreSQL right outer join

SELECT a, fruit_a, b, fruit_b FROM basket_a RIGHT JOIN basket_b ON fruit_a = fruit_b WHERE a IS NULL;

PostgreSQL right join

SELECT a, fruit_a, b, fruit_b FROM basket_a RIGHT JOIN basket_b ON fruit_a = fruit_b;

INTERSECT example

SELECT name FROM Students INTERSECT SELECT name FROM Contacts;

Minus example

SELECT name FROM Students MINUS SELECT name FROM Contacts;

union and union all

SELECT name FROM Students UNION SELECT name FROM Contacts; SELECT name FROM Students UNION ALL SELECT name FROM Contacts;

Write a sql query to fetch "names" from table "contacts" that are neither present in "accounts.name" nor in "registry.name";

SELECT names FROM contacts MINUS (SELECT names FROM accounts UNION SELECT names FROM registry);

Different kinds of privileges in PostgreSQL

SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE

What is CLAUSE?

SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records. Example - Query that has WHERE condition Query that has HAVING condition.

What is SQL?

SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.

Unique Index.

This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.

What is a join?

This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.

One-to-Many & Many-to-One

This is the most commonly used relationship where a record in a table is associated with multiple records in the other table.

Many-to-Many

This is used in cases when multiple instances on both sides are needed for defining a relationship.

Self Referencing Relationships

This is used when a table needs to define a relationship with itself.

First Normal Form (1NF)

This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.

Clustered Index.

This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.

Write a SQL statement to delete the table 'Temporary' while keeping its relations intact;

Truncate table temporary;

examples showing WHERE part having different LIKE clause with '%' and '_' operators

WHERE SALARY::text LIKE '200%' Finds any values that start with 200 WHERE SALARY::text LIKE '%200%' Finds any values that have 200 in any position WHERE SALARY::text LIKE '_00%' Finds any values that have 00 in the second and third positions WHERE SALARY::text LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length WHERE SALARY::text LIKE '%2' Finds any values that end with 2 WHERE SALARY::text LIKE '_2%3' Finds any values that have 2 in the second position and end with a 3 WHERE SALARY::text LIKE '2___3' Finds any values in a five-digit number that start with 2 and end with 3

GROUP group

A group to whom to grant privileges.

Table

object that contains records (rows) and fields (columns)

Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.

select * from city where population > 100000 and countrycode = 'USA';

Avg()

select avg(purch_amt) from orders;

Produce a count of the number of facilities that have a cost to guests of 10 or more.

select count(*) from cd.facilities where guestcost >= 10;

Count Aggregate function

select count(*) from cd.facilities;

Pros

- Good with large group projects - Strong documentation - Priority is on the long term or end goal rather then each step

Schema

- layout that outlines all of the database objects - Collection of tables

Query a count of the number of cities in CITY having a Population larger than 100000

1 SELECT COUNT(ID) FROM CITY WHERE POPULATION > 100000;

Multiplicity

1 to 1: one record in one table corresponds to exactly one record in another table - One person has one SSN and one SSN relates to one person 1 to Many: one record in one table can ref many records in another table but all of the records only point to the one record in the first table - One customer can have many orders Many to Many: each record in one table can point to multiple records in another table and vice versa - One student can have many classes and a class can have many different students - Table in btw two table to help manage joins (join tables)

What is a trigger?

A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database. Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.

What is DBMS?

A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.

What is a Foreign Key?

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refer to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.The table with the foreign key constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.

What is a unique key?

A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns. A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key. There can be many unique constraint defined per table, but only one Primary key constraint defined per table.

SQL Composite Key

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness. Sometimes more than one attributes are needed to uniquely identify an entity. A primary key that is made by the combination of more than one attribute is known as a composite key.

What is Cursor? How to use a Cursor?

A database cursor is a control structure that allows for traversal of records in a database. Cursors, in addition, facilitates processing after traversal, such as retrieval, addition and deletion of database records. They can be viewed as a pointer to one row in a set of rows.

What is an Index? Explain its different types.

A database index is a data structure that provides quick lookup of data in a column or columns of a table. It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure. CREATE INDEX index_name /* Create Index */ ON table_name (column_1, column_2); DROP INDEX index_name; /* Drop Index */

What is a foreign key?

A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.

What is a Query?

A query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.

First Normal Form

A relation is in first normal form if every attribute in that relation is a single-valued attribute. If a relation contains composite or multi-valued attribute, it violates the first normal form.

Second Normal Form

A relation is in second normal form if it satisfies the conditions for first normal form and does not contain any partial dependency. A relation in 2NF has no partial dependency, i.e., it has no non-prime attribute that depends on any proper subset of any candidate key of the table. Often, specifying a single column Primary Key is the solution to the problem.

Third Normal Form

A relation is said to be in the third normal form, if it satisfies the conditions for second normal form and there is no transitive dependency between the non-prime attributes, i.e.,all non-prime attributes are determined only by the candidate keys of the relation and not by any other non-prime attribute.

What is a Self-Join?

A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query. SELECT A.emp_id AS "Emp_ID",A.emp_name AS "Employee", B.emp_id AS "Sup_ID",B.emp_name AS "Supervisor" FROM employee A, employee B WHERE A.emp_sup = B.emp_id;

PUBLIC

A short form representing all users.

What is a Recursive Stored Procedure?

A stored procedure which calls itself until a boundary condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required. Some SQL programming languages limit the recursion depth to prevent an infinite loop of procedure calls from causing a stack overflow, which slows down the system and may lead to system crashes.

What is a Subquery?

A subquery is a query within another query, also known as nested query or inner query . It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively. SELECT name, email, mob, address FROM myDb.contacts WHERE roll_no IN ( SELECT roll_no FROM myDb.students WHERE subject = 'Maths');

What are tables and Fields?

A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields but can have any number of rows which is called record.

1NF

All table cells should be atomic aka should contain a single value - Each record should be unique (have a PK)

Cons

Harder to document - Harder to use with a large group and keep track of what everyone is doing

What is an ALIAS command?

ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column. Example-. Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID Here, st refers to alias name for student table and Ex refers to alias name for exam table.

Write a SQL statement to add a foreign key col_fk in table_y that references col_pk in table_x.

ALTER TABLE table_y ADD FOREIGN KEY (col_fk)REFERENCES table_x (col_pk);

Dev Team

Actual members of the team completing the work

Execution of the test

Actually run them

Durability

After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure. For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.

What are aggregate and scalar functions?

Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on the input value. Example -. Aggregate - max(), count - Calculated with respect to numeric. Scalar - UCASE(), NOW() - Calculated with respect to strings.

common aggregate functions in postgresql

Aggregate functions perform a calculation on a set of rows and return a single row. PostgreSQL provides all standard SQL's aggregate functions as follows: AVG() - return the average value. COUNT() - return the number of values. MAX() - return the maximum value. MIN() - return the minimum value. SUM() - return the sum of all or distinct values. We often use the aggregate functions with the GROUP BY clause in the SELECT statement. In these cases, the GROUP BY clause divides the result set into groups of rows and the aggregate functions perform a calculation on each group e.g., maximum, minimum, average, etc. You can use aggregate functions as expressions only in the following clauses: SELECT clause. HAVING clause.

Atomicity

All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are. For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.

Relational

All data is related

What is an Alias or correlation name in SQL?

An alias is a feature of SQL that is supported by most, if not all, RDBMSs. It is a temporary name assigned to the table or table column for the purpose of a particular SQL query. In addition, aliasing can be employed as an obfuscation technique to secure the real names of database fields. A table alias is also called a correlation name .

Entity

An entity can be a real-world object, either tangible or intangible, that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities. Each entity has some associated properties that provide it an identity.

What is an Index?

An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

Compactness

As a column can store a single type of value, it is stored in a compact way.

Transaction ACID Properties

Atomic, Consistent, Isolation, Durability

ACID

Atomicity, Consistency, Isolation, Durability

Properties of Transactions

Atomicity, Consistency, Isolation, Durability

Transaction Control

BEGIN TRANSACTION, COMMIT, ROLLBACK

Product Owner

Brings interest of customer to team

Case Statements

CASE is effectively like if/switch statements in other languages, with a form as shown in the query. To add a 'middling' option, we would simply insert another when...then section. select name, case when (monthlymaintenance > 100) then 'expensive' else 'cheap' end as cost from cd.facilities;

Common Scalar functions

CHARACTER_LENGTH (string), CONCAT (string1, string2), LEFT (string, count), RIGHT (string, count), REVERSE(string)

Postgres database commands

CREATE DATABASE, SELECT DATABASE, DROP DATABASE

Write an SQL statement to create a Unique Index "my_index" on "my_table" for fields "column_1" & "column_2"

CREATE UNIQUE INDEX my_index ON my_table (column_1, column_2);

Certain conditions need to be met before executing The UNION operator, The MINUS operator, The INTERSECT clause

Certain conditions need to be met before executing either of the above statements in SQL - Each SELECT statement within the clause must have the same number of columns The columns must also have similar data types The columns in each SELECT statement should necessarily have the same order

What is the difference between Cluster and Non-Cluster Index?

Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index. A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.

What is the difference between Clustered and Non-clustered index?

Clustered index modifies the way records are stored in a database based on the indexed column. Non-clustered index creates a separate entity within the table which references the original table. Clustered index is used for easy and speedy retrieval of data from the database, whereas, fetching records from the non-clustered index is relatively slower. In SQL, a table can have a single clustered index whereas it can have multiple non-clustered indexes.

Clustered

Clustered indexes are indexes whose order of the rows in the database correspond to the order of the rows in the index. This is why only one clustered index can exist in a given table, whereas, multiple non-clustered indexes can exist in the table.

What is collation?

Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters. ASCII value can be used to compare these character data.

What is Collation? What are the different types of Collation Sensitivity?

Collation refers to a set of rules that determine how data is sorted and compared. Rules defining the correct character sequence are used to sort the character data. It incorporates options for specifying case-sensitivity, accent marks, kana character types and character width. Below are the different types of collation sensitivity: Case sensitivity: A and a are treated differently. Accent sensitivity: a and á are treated differently. Kana sensitivity: Japanese kana characters Hiragana and Katakana are treated differently. Width sensitivity: Same character represented in single-byte (half-width) and double-byte (full-width) are treated differently.

PBI (product backlog item)

Collection of tasks

Non Relational

Collection of unrelated information

How to fetch common records from two tables?

Common records result set can be achieved by -. Select studentID from student INTERSECT Select StudentID from Exam

What is a Cross-Join?

Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN. SELECT stu.name, sub.subject FROM students AS stu CROSS JOIN subjects AS sub;

What is Cross-Join?

Cross join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table. If suppose, WHERE clause is used in cross join then the query will work like an INNER JOIN.

Special Mention

Cucumber (end-to-end testing)

SQL Sub Languages

DDL - Data Definition Language, DML - Data Manipulation Language, DQL - Data Query Language, DCL - Data Control Language, TCL - Transaction Control Language

Working with SQL Cursor

DECLARE a cursor after any variable declaration. The cursor declaration must always be associated with a SELECT Statement. Open cursor to initialize the result set. The OPEN statement must be called before fetching rows from the result set. FETCH statement to retrieve and move to the next row in the result set. Call the CLOSE statement to deactivate the cursor. Finally use the DEALLOCATE statement to delete the cursor definition and release the associated resources.

What is data Integrity?

Data Integrity defines the accuracy and consistency of data stored in a database. It can also define integrity constraints to enforce business rules on the data when it is entered into the application or database.

Consistency

Data is in a consistent state when a transaction starts and when it ends. For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

What is a Database?

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.

What is Datawarehouse?

Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data have a subset of data called Data Marts.

What is Denormalization?

DeNormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

Write a SQL query to remove first 1000 records from table 'Temporary' based on 'ID'.

Delete from temporary where id < 1000;

What is Denormalization?

Denormalization is the inverse process of normalization, where the normalized schema is converted into a schema which has redundant information. The performance is improved by using redundancy and keeping the redundant data consistent. The reason for performing denormalization is the overheads produced in query processor by an over-normalized structure.

GRANT command

Depending on the type of the object (table, function, etc.,), privileges are applied to the object. To assign privileges to the users, the GRANT command is used. GRANT privilege [, ...] ON object [, ...] TO { PUBLIC | GROUP group | username }

Effort Level

Determining how difficult a task will be - Physical number or value description

Burndown chart

Diagram or depiction that shows how much work is left in the sprint - Similar to kanban project board

Isolation

Enables transactions to operate independently of and transparent to each other.

Atomicity

Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.

Transaction Consistency

Ensures that the database properly changes states upon a successfully committed transaction.

Durability

Ensures that the result or effect of a committed transaction persists in case of a system failure.

UNION ALL

Fetch the union of queries with duplicates

What are the various forms of Normalization?

First Normal Form, Second Normal Form, Third Normal Form

FULL OUTER JOIN

First, an inner join is performed. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2. In addition, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added. SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

RIGHT OUTER JOIN

First, an inner join is performed. Then, for each row in table T2 that does not satisfy the join condition with any row in table T1, a joined row is added with null values in columns of T1. This is the converse of a left join; the result table will always have a row for each row in T2. SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

What are all different types of collation sensitivity?

Following are different types of collation sensitivity -. Case Sensitivity - A and a and B and b. Accent Sensitivity. Kana Sensitivity - Japanese Kana characters. Width Sensitivity - Single byte character and double byte character.

Full Join

Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.

Testing

Function vs Non Functional

Daily Stand Up

Generally 10 to 15 min - Physically stand up to distance self from keyboard and focus on talking - Go over - What's been completed since last SU - What you're currently working on - Any blockers - NOT a time to troubleshoot

sql cursor vs index

In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. Indexes: Indexes help us retrieve data from tables quicker. When there are thousands of records in a table, retrieving information will take a long time.

Regression testing

Incremental approach to testing

Inner Join.

Inner join return rows when there is at least one match of rows between the tables.

PostgreSQL UNION ALL with ORDER BY clause example

SELECT * FROM top_rated_films UNION ALL SELECT * FROM most_popular_films ORDER BY title;

Unit Tests:

JUnit, TestNG, JMockit, NUnit

Integration Tests

JUnit/TestNG, Mockito, REST-Assured, Spring Test

Testing Pyramid

Links together level of isolation with how fast or slow it is Slower tests at top Faster tests at bottom

What are local and global variables and their differences?

Local variables are the variables which can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred or used. Variables can be created whenever that function is called. Global variables are the variables which can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.

Integration testing

Making sure that two pieces of code are able to comm with each other with no errors

Pros

More flexible - More communication with customer

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

Mostly used with aggregate functions. This is counting the customer ids by country.

3NF

Must be in 2NF already - Cannot have any transitive functional dependencies between tables or within DB - Essentially extends 2NF to entire DB

2NF

Must be in first normal form already - No functional dependencies within tables - Should not be able to derive any info in that table from other data in that table

NonClustered Index.

NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.

Online Transaction Processing

OLTP stands for Online Transaction Processing, is a class of software applications capable of supporting transaction-oriented programs. An essential attribute of an OLTP system is its ability to maintain concurrency. To avoid single points of failure, OLTP systems are often decentralized. These systems are usually designed for a large number of users who conduct short transactions. Database queries are usually simple, require sub-second response times and return relatively few records.

What are the differences between OLTP and OLAP?

OLTP stands for Online Transaction Processing, is a class of software applications capable of supporting transaction-oriented programs. An important attribute of an OLTP system is its ability to maintain concurrency. OLTP systems often follow a decentralized architecture to avoid single points of failure. These systems are generally designed for a large audience of end users who conduct short transactions. Queries involved in such databases are generally simple, need fast response times and return relatively few records. Number of transactions per second acts as an effective measure for such systems.

What is Online Transaction Processing (OLTP)?

Online Transaction Processing (OLTP) manages transaction based applications which can be used for data entry, data retrieval and data processing. OLTP makes data management simple and efficient. Unlike OLAP systems goal of OLTP systems is serving real-time transactions. Example - Bank Transactions on a daily basis.

Consistency

Operations against columns of same data type give consistent results and are usually the fastest.

Design

Part of test plan

AUTO INCREMENT

PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases. CREATE TABLE tablename ( colname SERIAL );

SQL order of execution

Processed from inside out.

Performance

Proper use of data types gives the most efficient storage of data. The values stored can be processed quickly, which enhances the performance.

Validation

Proper use of data types implies format validation of data and rejection of data outside the scope of data type.

Sprint TimeLine

Prospective aka Sprint Plan - Time to set goals - Go over product backlog - Determine what tasks need to be done - Decide what MVP (minimum viable product) is

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored into the tables.

Database

Relational and Non Relational

Relationships

Relations or links between entities that have something to do with each other. For example - The employees table in a company's database can be associated with the salary table in the same database.

Right Join

Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.

UI Tests

Same as UA Tests

Scrum

Scrum is the type of Agile framework. It is a framework within which people can address complex adaptive problem while productivity and creativity of delivering product is at highest possible values. Scrum uses Iterative process.

Matching a pattern anywhere using the % wildcard twice

Search for a student in the database where he/she has a K in his/her first name. SELECT * FROM students WHERE first_name LIKE '%K%'

How can you create an empty table from an existing table?

Select * into studentcopy from student where 1=2 Here, we are copying student table to another table with the same structure with no rows copied.

How to select unique records from a table

Select DISTINCT StudentID, StudentName from Student.

What is the command used to fetch first 5 characters of the string

Select SUBSTRING(StudentName,1,5) as studentname from student Select LEFT(Studentname,5) as studentname from student

System Tests

Selenium, TestComplete, Katalon Studio

What is Self-Join?

Self-join is set to be query used to compare to itself. This is used to compare values in a column with other values in the same column in the same table. ALIAS ES can be used for the same table comparison.

Agile Buzzwords

Sprint - Tasks - MVP - Scrum/Kanban - Sprint timeline - Prospective - Retrospective

What is a stored procedure?

Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.

Advantages and Disadvantages of Stored Procedure?

Stored procedure can be used as a modular programming - means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data. Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.

Subqueries with the INSERT Statement

Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions. INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;

Subqueries with the SELECT Statement

Subqueries are most frequently used with the SELECT statement. SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;

rules that subqueries must follow

Subqueries must be enclosed within parentheses. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery. Subqueries that return more than one row can only be used with multiple value operators, such as the IN, EXISTS, NOT IN, ANY/SOME, ALL operator. The BETWEEN operator ca

User Stories

Take a user and tell a story from the user's perspective - A way of approaching testing and functionality - Short simple descriptions of a feature - "As an x, I want to x, so that I can x"

Using the % wildcard to perform a simple search

The % wildcard matches zero or more characters of any type and can be used to define wildcards both before and after the pattern. Search a student in your database with first name beginning with the letter K. SELECT * FROM students WHERE first_name LIKE 'K%'

STLC

The Software Testing Life Cycle - Planning and Analysis - Design - Develop Test Cases - Choose the environment for test - Execution of the test - Conclusion

PostgreSQL UNION operator

The UNION operator combines result sets of two or more SELECT statements into a single result set. Will remove duplicate rows. SELECT * FROM top_rated_films UNION SELECT * FROM most_popular_films;

Using the _ wildcard to match pattern at a specific position

The _ wildcard matches exactly one character of any type. It can be used in conjunction with % wildcard. This query fetches all students with letter K at the third position in their first name. SELECT * FROM students WHERE first_name LIKE '__K%'

Matching patterns for specific length

The _ wildcard plays an important role as a limitation when it matches exactly one character. It limits the length and position of the matched results. For example - SELECT * FROM students WHERE first_name LIKE '___%' SELECT * FROM students WHERE first_name LIKE '____'

Waterfall

The waterfall model is a breakdown of project activities into linear sequential phases, where each phase depends on the deliverables of the previous one and corresponds to a specialisation of tasks. The approach is typical for certain areas of engineering design

PostgreSQL inner join

The following statement joins the first table (basket_a) with the second table (basket_b) by matching the values in the fruit_a and fruit_b columns: SELECT a, fruit_a, b, fruit_b FROM basket_a INNER JOIN basket_b ON fruit_a = fruit_b;

Third Normal Form (3NF)

This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.

Isolation

The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized. For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.

Monetary Types

The money type stores a currency amount with a fixed fractional precision. Values of the numeric, int, and bigint data types can be cast to money. Using Floating point numbers is not recommended to handle money due to the potential for rounding errors. ie money.

username

The name of a user to whom to grant privileges. PUBLIC is a short form representing all users.

object

The name of an object to which to grant access. The possible objects are: table, view, sequence

Non-Clustered Index

The only difference between clustered and non-clustered indexes is that the database manager attempts to keep the data in the database in the same order as the corresponding keys appear in the clustered index.

What are all the different types of indexes?

There are three types of indexes

What are the types of subquery?

There are two types of subquery - Correlated and Non-Correlated. A correlated subquery cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query. A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query.

Database Management System (DBMS)

The software responsive for create read update and management of a database.

Subqueries with the DELETE Statement

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above. DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );

Subqueries with the UPDATE Statement

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement. UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );

ALIAS

The use of table aliases means to rename a table in a particular PostgreSQL statement. Renaming is a temporary change and the actual table name does not change in the database. SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID;

SQL timestamps evaluations and formatting

They're formatted in descending order of magnitude: YYYY-MM-DD HH:MM:SS.nnnnnn. We can compare them just like we might a unix timestamp, although getting the differences between dates is a little more involved (and powerful!). select memid, surname, firstname, joindate from cd.members where joindate >= '2012-09-01';

One-to-One

This can be defined as the relationship between two tables where each record in one table is associated with the maximum of one record in the other table.

What are all types of user defined functions?

Three types of user defined functions are. Scalar Functions. Inline Table valued functions. Multi statement valued functions. Scalar returns unit, variant defined the return clause. Other two types return table as a return.

Commit and rollback

To assure the ACID properties of a transaction, any changes made to data in the course of a transaction must be committed or rolled back. When a transaction completes normally, a transaction processing system commits the changes made to the data; that is, it makes them permanent and visible to other transactions. When a transaction does not complete normally, the system rolls back (or backs out) the changes; that is, it restores the data to its last consistent state. Resources that can be rolled back to their state at the start of a transaction are known as recoverable resources: resources that cannot be rolled back are nonrecoverable.

SQL PRIMARY KEY on ALTER TABLE

To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL: ALTER TABLE PersonsADD PRIMARY KEY (ID);

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL: ALTER TABLE PersonsDROP PRIMARY KEY;

ROLLBACK

To rollback the changes. he ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

COMMIT

To save the changes, alternatively you can use END TRANSACTION command. The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.

BEGIN TRANSACTION

To start a transaction. Transactions can be started using BEGIN TRANSACTION or simply BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command is encountered. But a transaction will also ROLLBACK if the database is closed or if an error occurs.

What is Union, minus and Interact commands?

UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables. MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set. INTERSECT operator is used to return rows returned by both the queries.

An action query or UPDATE command example

UPDATE application SET status = 1 WHERE status = 0;

Functional Tests

Unified Functional Testing, Tricentis Tosca, SoapUI

Unique Index

Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values. Once a unique index has been defined for a table, uniqueness is enforced whenever keys are added or changed within the index. CREATE UNIQUE INDEX myIndex ON students (enroll_no);

Omitting the patterns using the NOT keyword

Use the NOT keyword to select records that don't match the pattern. This query returns all students whose first name does not begin with K. SELECT * FROM students WHERE first_name NOT LIKE 'K%'

What is user defined functions?

User defined functions are the functions written to use that logic whenever required. It is not necessary to write the same logic several times. Instead, function can be called or executed whenever needed.

Retrospective

What was completed - What still needs to be completed - Lessons learned - After retrospective, go over prospective the next day

Story pointing

When dev's give an effort level for a task - Figuring out how difficult a task will be - Multiple people pooling together their effort level of a tasks

PRIVILEGES

Whenever an object is created in a database, an owner is assigned to it. The owner is usually the one who executed the creation statement. For most kinds of objects, the initial state is that only the owner (or a superuser) can modify or delete the object. To allow other roles or users to use it, privileges or permission must be granted.

PostgreSQL UNION ALL example

Will not remove duplicates SELECT * FROM top_rated_films UNION ALL SELECT * FROM most_popular_films;

PostgreSQL NOT IN operator

You can combine the IN operator with the NOT operator to select rows whose values do not match the values in the list. SELECT customer_id, rental_id, return_date FROM rental WHERE customer_id <> 1 AND customer_id <> 2;

PostgreSQL IN operator

You use IN operator in the WHERE clause to check if a value matches any value in a list of values. SELECT customer_id, rental_id, return_date FROM rental WHERE customer_id IN (1, 2) ORDER BY return_date DESC;

User Acceptance Tests

[same as System/Functional Tests]

Online Analytical Processing

a class of software programs which are characterized by relatively low frequency of online transactions. Queries are often too complex and involve a bunch of aggregations. For OLAP systems, the effectiveness measure relies highly on response time. Such systems are widely used for data mining or maintaining aggregated, historical data, usually in multi-dimensional schemas.

Date/Time Types

a full set of SQL date and time types, ie timestamp [(p)] [without time zone ], time [ (p)] with time zone

A schema

a named collection of tables

An operator

a reserved word or a character used primarily in a PostgreSQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. + - * /

A transaction

a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program. A transaction is the propagation of one or more changes to the database. For example, if you are creating a record, updating a record, or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

DML - Data Manipulation Language

a. Adds, removes or edits data in your table b. DELETE c. UPDATE d. INSERT e. SELECT

DDL - Data Definition Language

a. Defines the tables and how to organize your data b. CREATE c. ALTER d. DROP e. TRUNCATE

DCL - Data Control Language

a. Handles permissions of data b. GRANT c. REVOKE d. DENY

TCL - Transaction Control Language

a. Handles transactions b. Transaction - set of steps that the user creates c. COMMIT d. ROLLBACK e. SAVE POINT (not commonly used) f. SET TRANSACTION (not commonly used)

DQL - Data Query Language

a. Reads the data from the database b. SELECT

Planning and Analysis

a. Test Strategy - high level outline, entire company strategy b. Test Plan - low level doc, outline features to be tested, environment c. Test Log

Choose the environment for test

a. What will the testing environment b. Combo of hardware and software? i. Local comp? ii. Which OS? iii. What Language?

Conclusion

a. Wrap up and continue to monitor b. Generate report c. End of the life cycle d. Make sure tests are still applicable

Develop Test Cases

a. Write code b. Cover all functionality c. Think of all edge cases

Atomicity

all parts of transaction must succeed or non of them should

HAVING clause

allows us to pick out particular rows where the function's result meets some condition. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2; SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

PostgreSQL functions

also known as Stored Procedures, allow you to carry out operations that would normally take several queries and round trips in a single function within the database. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code.

Write a SQL statement to add primary key constraint 'pk_a' for table 'table_a' and fields 'col_b, col_c.

alter table table_a add constraint pk_a primary key (col_b, col_c);

LEFT OUTER JOIN

an inner join is performed first. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1. SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

Non-unique indexes

are not used to enforce constraints on the tables with which they are associated. Instead, non-unique indexes are used solely to improve query performance by maintaining a sorted order of data values that are used frequently.

AND and OR operators

are used to combine multiple conditions to narrow down selected data in a PostgreSQL statement. These two operators are called conjunctive operators. SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN]; SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

Binary Data Types

bytea, 1 or 4 bytes plus the actual binary string

A non-correlated subquery

can be considered as an independent query and the output of subquery is substituted in the main query.

A correlated subquery

cannot be considered as an independent query, but it can refer the column in a table listed in the FROM of the main query.

HAVING

clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since WHERE clause cannot filter aggregated records. SELECT COUNT(studentId), country FROM myDB.students WHERE country != "INDIA" GROUP BY country HAVING COUNT(studentID) > 5;

GROUP BY

clause in SQL is used to group records with identical data and can be used in conjuction with some aggregation functions to produce summarized results from the database.

ORDER BY clause

clause is used to sort the data in ascending or descending order, based on one or more columns. SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; SELECT * FROM COMPANY ORDER BY AGE ASC; / SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

union keyword

combined list

The UNION operator

combines and returns the result-set retrieved by two or more SELECT statements.

The SQL Statement

comprised of tokens where each token can represent either a keyword, identifier, quoted identifier, constant, or special character symbol.

INNER JOIN

creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows, which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of table1 and table2 are combined into a result row. SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

PostgreSQL Triggers

database callback functions, which are automatically performed/invoked when a specified database event occurs. PostgreSQL trigger can be specified to fire Before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted) After the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed) Instead of the operation (in the case of inserts, updates or deletes on a view)

Write a SQL statement to wipe a table "Temporary" from memory.

drop table temporary;

Isolation

each transaction must be independent of all other transactions or happening in parallel

partial dependency

i.e., it has no non-prime attribute that depends on any proper subset of any candidate key of the table. Often, specifying a single column Primary Key is the solution to the problem.

The INTERSECT clause

in SQL combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.

The MINUS operator

in SQL is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first.

An action query

is a query that makes changes to or moves many records in just one operation.

A subquery or Inner query or Nested query

is a query within another PostgreSQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, etc.

GROUP BY clause

is used in collaboration with the SELECT statement to group together those rows in a table that have identical data. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups. SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

UNION ALL Clause

is used to combine the results of two SELECT statements including duplicate rows. The same rules that apply to UNION apply to the UNION ALL operator as well. SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION ALL SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

DELETE Query

is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete the selected rows. Otherwise, all the records would be deleted. DELETE FROM table_name WHERE [condition];

UPDATE Query

is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update the selected rows. Otherwise, all the rows would be updated. UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];

EXPRESSIONS

like formulas and they are written in query language. You can also use to query the database for specific set of data. SELECT column1, column2, columnN FROM table_name WHERE [CONDITION | EXPRESSION];

user-defined functions

like functions in any other programming language that accept parameters, perform complex calculations, and return a value. They are written to use the logic repetitively whenever required

A CROSS JOIN ?

matches every row of the first table with every row of the second table. If the input tables have x and y columns, respectively, the resulting table will have x+y columns. Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to use them only when appropriate. SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

Agile SDLC

model is a combination of iterative and incremental process models with focus on process adaptability and customer satisfaction by rapid delivery of working software product. Agile Methods break the product into small incremental builds. These builds are provided in iterations.

Locks or Exclusive Locks or Write Locks

prevent users from modifying a row or an entire table. Rows modified by UPDATE and DELETE are then exclusively locked automatically for the duration of the transaction. This prevents other users from changing the row until the transaction is either committed or rolled back. The only time when users must wait for other users is when they are trying to modify the same row. If they modify different rows, no waiting is necessary. SELECT queries never have to wait. The database performs locking automatically. In certain cases, however, locking must be controlled manually. Manual locking can be done by using the LOCK command. It allows specification of a transaction's lock type and scope. LOCK [ TABLE ] name IN lock_mode;

Normalization

process by which we reduce redundancy in a database and helps with querying data. 1NF, 2NF, 3NF. INDUSTRY STANDARD IS 3NF

WITH query ???

provides a way to write auxiliary statements for use in a larger query. It helps in breaking down complicated and large queries into simpler forms, which are easily readable.

Views

pseudo-tables. That is, they are not real tables; nevertheless appear as ordinary tables to SELECT. A view can represent a subset of a real table, selecting certain columns or certain rows from an ordinary table. A view can even represent joined tables. Because views are assigned separate permissions, you can use them to restrict table access so that the users see only specific rows or columns of a table. CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY;

What type of integrity constraint does the foreign key ensure?

referential integrity

RDBMS

relational database management system - Software designed to manage a database - Examples - PostgreSQL, Oracle SQL, Maria DB, MySQL, SQLite, Amazon Aurora, Microsoft SQL Server

User-defined table-valued functions

return a table as output.

A scalar function

returns a single value based on the input value. Following are the widely used SQL scalar functions: LEN() - Calculates the total length of the given field (column). UCASE() - Converts a collection of string values to uppercase characters. LCASE() - Converts a collection of string values to lowercase characters. MID() - Extracts substrings from a collection of string values in a table. CONCAT() - Concatenates two or more strings. RAND() - Generates a random collection of numbers of given length. ROUND() - Calculates the round off integer value for a numeric field (or decimal point values). NOW() - Returns the current data & time. FORMAT() - Sets the format to display a collection of values.

Inline

returns a table data type based on a single SELECT statement.

Multi-statement

returns a tabular result-set but, unlike inline, multiple SELECT statements can be used inside the function body.

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer. Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

select country.continent,floor(avg(city.population)) from country inner join city on country.Code=city.countrycode group by country.continent;

How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.

select distinct surname from cd.members order by surname limit 10;

produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.

select distinct surname from cd.members order by surname limit 10;

get the first and last name of the last member(s) who signed up - not just the date

select firstname, surname, joindate from cd.members where joindate = (select max(joindate) from cd.members); You have to use the where clause as a sub query because it is needed to specify what column we are inspecting.

Using the max aggregate function

select max(joindate) as latest from cd.members; The (as latest) is the column label we are creating.

SQL Substring practice problem. Query the Name of any student in STUDENTS who scored higher than Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

select name from students where marks > 75 order by substr(Name, -3), ID;

Produce a count of the number of recommendations each member has made. Order by member ID.

select recommendedby, count(*) from cd.members where recommendedby is not null group by recommendedby order by recommendedby;

Query the total population of all cities in CITY where District is California.

select sum(population) from city where district = 'California';

You want a combined list of all surnames and all facility names

select surname from cd.members union select name from cd.facilities; Basically two select statements with a union command between them.

Indexes

special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. CREATE INDEX index_name ON table_name (column_name);

INSERT INTO

statement allows one to insert new rows into a table. One can insert a single row at a time or several rows as a result of a query. INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN);

Relation data base management system

stores data in the form of tables. Tabular Structure. Can have many users.

Write a SQL statement to find the number of salesman currently listing for all of their customers.

sum() function; select sum(purch_amt) from orders;

Scrum Master

team lead

Negative

testing a bad input to make sure the program fails gracefully, test will succeed but the program will "fail"

Positive

testing a correct input to hopefully return a correct output

Non Functional

testing for usability, reliability, performance

White Box

testing specific components within the app

Functional

testing what your code actually needs to do

Black Box

testing without knowledge of how the program accomplishes a task or turns an input into an output

Character Types

text, char(n)

Consistency

the DB stays intact before and after the transaction

Array Type

the opportunity to define a column of a table as a variable length multidimensional array.

Constraints

the rules enforced on data columns on table. These are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database. NOT NULL Constraint − Ensures that a column cannot have NULL value. UNIQUE Constraint − Ensures that all values in a column are different. PRIMARY Key − Uniquely identifies each row/record in a database table. FOREIGN Key − Constrains data based on columns in other tables. CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions. EXCLUSION Constraint − The EXCLUDE constraint ensures that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.

Boolean Type

the standard SQL type Boolean

PostgreSQL NULL

the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different from a zero value or a field that contains spaces. UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

IS NOT NULL operator

to list down all the records where SALARY is not NULL. SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;

Durability

transactions fail gracefully and don't cause the DB to crash, even if the DB does crash it can still hold all it's updates

Transactions

units of sql commands, ACID

DISTINCT keyword

used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records. SELECT DISTINCT name FROM COMPANY;

ALTER TABLE

used to add, delete or modify columns in an existing table. You would also use ALTER TABLE command to add and drop various constraints on an existing table. ALTER TABLE table_name DROP PRIMARY KEY;

Joins clause

used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. Join Types in PostgreSQL are − The CROSS JOIN The INNER JOIN The LEFT OUTER JOIN The RIGHT OUTER JOIN The FULL OUTER JOIN

UNION clause/operator

used to combine the results of two or more SELECT statements without returning any duplicate rows. SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

TRUNCATE TABLE

used to delete complete data from an existing table. You can also use DROP TABLE command to delete complete table but it would remove complete table structure from the database and you would need to re-create this table once again if you wish to store some data. TRUNCATE TABLE COMPANY;

SELECT statement

used to fetch the data from a database table, which returns data in the form of result table. These result tables are called result-sets. SELECT * FROM table_name;

LIMIT clause

used to limit the data amount returned by the SELECT statement. SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]

LIKE operator

used to match text values against a pattern using wildcards. There are two wildcards used in conjunction with the LIKE operator − The percent sign (%) The underscore (_)

DROP TABLE statement

used to remove a table definition and all associated data, indexes, rules, triggers, and constraints for that table. DROP TABLE table_name; A schema can also contain views, indexes, sequences, data types, operators, and functions. Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

WHERE clause

used to specify a condition while fetching the data from single table or joining with multiple tables. SELECT column1, column2, columnN FROM table_name WHERE [search_condition] IE SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

CREATE TYPE SQL

users can create their own custom data type

privilege

values could be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.

Silent features of Scrum are

● Scrum is light-weighted framework ● Scrum emphasizes self-organization ● Scrum is simple to understand ● Scrum framework help the team to work together