other Flashcards
Database
A database is a collection of information that is organized so that it can be easily accessed, managed and updated.
Relational Database
A relational database is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables.
Tables : Rows and Columns
Tables can have hundreds, thousands, sometimes even millions of rows of data. These rows are often called records.
Tables can also have many columns of data. Columns are labeled with a descriptive name (say, age for example) and have a specific data type.
Relational Database Management System
A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database.
SQL - Structured Query Language
SQL (Structured Query Language) is a programming language used to communicate with data stored in a relational database management system. SQL syntax is similar to the English language, which makes it relatively easy to write, read, and interpret.
Many RDBMSs use SQL (and variations of SQL) to access the data in tables. For example, SQLite is a relational database management system. SQLite contains a minimal set of SQL commands (which are the same across all RDBMSs). Other RDBMSs may use other variants.
Why do we need SQL?
Allows users to access data in the relational database management systems.
Allows users to describe the data.
Allows users to define the data in a database and manipulate that data.
Allows to embed within other languages using SQL modules, libraries & pre-compilers.
Allows users to create and drop databases and tables.
Allows users to create view, stored procedure, functions in a database.
Allows users to set permissions on tables, procedures and views.
Primary Key
A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.
A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).
Foreign Key
A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key.
A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.
SQL Statements
DDL - Data Definition Language - CREATE, ALTER, DROP
DML - Data Manipulation Language - SELECT, INSERT, UPDATE, DELETE
DCL - Data Control Language - GRANT, REVOKE
TCL - Transaction Control Language - SAVEPOINT, ROLLBACK, COMMIT
SELECT Query
The SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets.
SELECT column1, column2, columnN FROM table_name;
If you want to fetch all the fields available in the field, then you can use the following syntax.
SELECT * FROM table_name;
SQL Where
The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records.
The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement.
Syntax :
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]
INSERT Query
The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.
Syntax :
There are two basic syntax’s of the INSERT INTO statement which are shown below.
INSERT INTO TABLE_NAME (column1, column2, column3,…columnN)
VALUES (value1, value2, value3,…valueN);
SQL Joins
The SQL Joins clause is 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.
Different Types Of Joins
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
SDLC
SDLC (Software Development Life Cycle) is the process of developing software through business needs, analysis, design, implementation and maintenance
Investigation
The 1st stage of SDLC is the investigation phase. During this stage, business opportunities and problems are identified, and information technology solutions are discussed. Multiple alternative projects may be suggested and their feasibility analyzed. Operational feasibility is assessed, and it is determined whether or not the project fits with the current business environment, and to what degree it addresses business objects. In addition, an economic feasibility investigation is conducted to judge the costs and benefits of the project. Technical feasibility must also be analyzed to determine if the available hardware and software resources are sufficient to meet expected specifications
The results of the feasibility study can then be compiled into a report, along with preliminary specifications.
When the investigation stage ends, a decision whether or not to move forward with the project should be made.
