SQL

Primary Key

A Primary key is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table, each table can have only one like id. We can generate ID automatically with the help of auto increment. Uniquely identifies a single row in the table Cannot have null value

Database

A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches.

What are Tables and Fields?

A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.

Logical Operators

ALL AND ANY BETWEEN EXISTS IN LIKE NOT OR SOME

AS

AS keyword - renames a column or table with an alias SELECT id AS 'ID', name AS 'Band Name' FROM bands; So it changed from id to ID and name to Band Name on the table's columns.

What is the difference between CHAR and VARCHAR2 datatype in SQL?

Both Char and Varchar2 are used for characters datatype but varchar2 is used for character strings of variable length whereas Char is used for strings of fixed length. For example, char(10) can only store 10 characters and will not be able to store a string of any other length whereas varchar2(10) can store any length i.e 6,8,2 in this variable.

What language MySQL has been written?

C & C++

Syntax to create database/table with column name.

CREATE DATABASE record_company; CREATE TABLE bands( name VARCHAR(255) NOT NULL ); This will create a database, table, with column name "name" with a max character limit of 255 and no blanks.

Copying a Table

CREATE TABLE AS statement to create a table from an existing table by copying the existing table's columns.

Back-end testing used with SQL?

Our ERP system like Yardi back-end testing would entail checking the business logic in the application layer. It means that date entered in the front end will be checked in the back-end database.

Query

Question or request for data. SELECT - selects data from a database FROM - specifies which table to select or delete data from WHERE - filters a result set to include only records that fulfill a specified condition

What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2 and Amazon Redshift are based on RDBMS.

Join

Join clause is used to combine rows from two or more tables, based on a related column between them.

Name different types of case manipulation functions available in SQL.

Lower - return the string in lower case Upper - return the string in uppercase Initcap - return the string with first letter in uppercase and rest of the letters in lowercase

Left Join

Returns all records from the left table, and matched records from the right table. The result is NULL from the right side, if there is no match.

Right Join

Returns all records from the right side, and the matched records from the left table. The result is NULL from the left side, when there is no match.

Full Outer Join

Returns all records when there is a match in either left or right table records.

Database Management System (DBMS)

Technology that manages the storage and retrieval of data from databases Data within the most common types of databases are typically modeled in rows and columns in a series of tables to make processing and data querying efficient. The data can be easily accessed, managed, modified, updated, controlled, and organized.

HAVING

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SELECT b.name AS band_name, COUNT (a.id) AS num_albums FROM bands AS b LEFT JOIN albumns AS a ON b.id=a.band_id GROUP BY b.id HAVING num_albums=1;

Join

The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two. - JOIN albums ON bands.id=albums.band_id; --> Table listed first bands = left and second table albums = right Inner Join - returns records that have matching values in both tables Right Join - returns all records from the right table, and the matched records from the left table Left Join - returns all records from the left table, and the matched records from the right table Full Join - returns all records when there is a match in either left or right table

What is the difference between BETWEEN and IN operators in SQL?

The between operator is used to fetch rows based on a range of values. SELECT * FROM students WHERE ROLL_NO BETWEEN 20 AND 30; The in operator is used to check for values contained in specific sets. SELECT * FROM students WHERE ROLL_NO IN (20,21,20);

Index

They're special lookup tables that the database search engine can use to speed up data retrieval. It's a pointer to data in a table, very similar to an index in the back of a book.

TCL

Transaction Control Language COMMIT - to save the changes ROLLBACK - roll back the changes SAVEPOINT - creates points within the groups of transactions in which to rollback to SET TRANSACTION - places a name on a transaction

TRUNCATE vs DELETE vs DROP

Truncate - DDL command using a table lock and the whole table is locked to remove all records, cannot use where clause and removes all rows from a table. Delete - deletes all records from a database table, DML command Drop - removes one or more table definitions and all data, indexes, triggers, constraints and permission specifications for those table

SQL

Structured Query Language. It is the standard language for relational database management systems. It is especially useful in handling organized data comprised of entities (variables) and relations between different entities of the data.

Wildcard Characters

* -> Represents zero or more characters -> bl* finds bl, black, blue, and blob % -> Represents %letter or letter% or %letter% options.

Foreign Key

-Foreign key maintains referential integrity by enforcing a link between the data in two tables. -The foreign key in the child table references the primary key in the parent table. -The foreign key constraint prevents actions that would destroy links between the child and parent tables.

Unique Key

-Uniquely identifies a single row in the table. -Multiple values allowed per table. -Can have null value

Constraints

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during creation of table or after creation using the ALTER TABLE command. They're used to limit the type of data that can go in a table. The constraints are: -NOT NULL - Restricts NULL value from being inserted into a column. -CHECK - Verifies that all values in a field satisfy a condition. -DEFAULT - Automatically assigns a default value if no value has been specified for the field. -UNIQUE - Ensures that all values in a column are different. -INDEX - Indexes a field providing faster retrieval of records. -PRIMARY KEY - Uniquely identifies each record/row in a table. -FOREIGN KEY - Ensures referential integrity for a record/row in another table child table to parent table.

DISTINCT

DISTINCT Keyword - will not show duplicates will only show unique names only SELECT DISTINCT name FROM albums;

SQL Languages

DQL - Data Query Lang DDL - Data Definition Lang ..schemas-database/objects/drop/alter/truncate/rename DML - Data Manipulation Lang-insert data in table, update data in table, delete records from table DCL - Data Control Lang-grant/revoke access TCL - Transaction Control Lang ... commit and restore

DCL

Data Control Language GRANT REVOKE

DDL

Data Definition Language CREATE ALTER - add, delete, or modify columns DROP - delete objects from database or table TRUNCATE - delete the data from a table but not the table itself

What do you mean by data integrity?

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

DML

Data Manipulation Language SELECT INSERT UPDATE DELETE

NULL

Field with no value. It is different from a zero value, it was left blank during record creation.

How did you use join in the workplace?

Here's an example: let's say we have two tables in a database that tracks sales from an e-commerce company. One table is called customers, and contains data individual customers, like first_name and email. The second table is called orders, and contains information on individual orders that have been placed — like order_date and product. Each order in our database is placed by a customer, but we don't keep the customer's information within the orders table. Why not? Because if the same customer placed multiple orders, and we kept track of customer information within the orders table, we'd be duplicating data unnecessarily. By separating customer information into its own customers table, we can reduce redundancy and make updates and changes much easier to handle. So, we include a field called customer_id within each record on the orders table. this ID is linked to a customer_id on the customers table, which contains non-redundant data for each individual customer. When we want to bring two tables together, we use a JOIN statement to combine data as necessary.

Does SQL support programming language features?

It does not support programming as it's not a programming language, it is a command language. There is no conditional statement in SQL like for loops or if..else, only commands are to be used to query, update, delete data in the database.

Have you done backend testing at your workplace and how?

It ensures the data contained in your database and its structure satisfy the project's requirements. All DB tests can be grouped into three backend testing types: - Structural testing deals with the database inner structure (i.e. metadata). It involves validation of Tables and all the other database objects that aren't directly accessible by users, like Columns, Keys, Indexes, Schema, Triggers, Functions, etc. It can also involve testing your DB servers. - Functional testing focuses on the way data is mapped from the frontend to the database. (e.g. whether clicking a button correctly affects the related tables/columns). - Non-functional testing checks how the database performs under the expected loads and extreme levels of stress. Security tests also belong here.

What is the difference between SQL and MySQL?

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

Aggregate Functions

SUM - adds together all the values in a column MIN and MAX - lowest and highest values in particular column AVG - average of group of selected values COUNT - counts how many rows are in a particular column SELECT band_id, COUNT (band_id) FROM ALBUMS GROUP BY band_id; Grouping bands by # of albums they had

Inner Join

Selects records that have matching values in both tables.

Trigger

Stored procedure that automatically runs when an event occurs in the database serve. DML triggers run when a user tries to modify data through a data manipulation language DML event. They are INSERT, UPDATE, or DELETE statements on a table or view. - The good reason to use DML SQL triggers is the case when you want to assure that a certain control shall be performed before or after the defined statement on the defined table. This could be the case when your code is all over the place, e.g. database is used by different applications, code is written directly in applications and you don't have it well-documented. - Ex. When you try to delete an employee, you want to check if it has related calls. If so, you'll prevent that delete and raise a custom exception

Data Types in SQL

int - integers datetime date time char varchar varchar(max) text

Datatypes in SQL

int, decimal, numeric, date, time, year, char, varchar, varchar(max)

Command to Save Work

spool c:/sqllab(date of your choice).doc