dbms

 

What is Data?

Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc.

click here : dbms unit 1 and 2

clicke here : dbms unit 3

Database Admin or Database Administrator(DBA)


DataBase Languages:


 Another category : TCL – Transaction Control Language.

1.DDL(Data Definition Language) : DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

 

Examples of DDL commands: 

  • CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).
  • DROP – is used to delete objects from the database.
  • ALTER-is used to alter the structure of the database.
  • TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.
  • COMMENT –is used to add comments to the data dictionary.
  • RENAME –is used to rename an object existing in the database.

2.DQL (Data Query Language) :

DQL statements are used for performing queries on the data within schema objects. The purpose of the DQL Command is to get some schema relation based on the query passed to it. 

Example of DQL: 

  • SELECT – is used to retrieve data from the database.

3.DML(Data Manipulation Language): The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. 


Examples of DML: 

  • INSERT – is used to insert data into a table.
  • UPDATE – is used to update existing data within a table.
  • DELETE – is used to delete records from a database table.

4.DCL(Data Control Language): DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions and other controls of the database system. 

Examples of DCL commands: 

  • GRANT-gives users access privileges to the database.
  • REVOKE-withdraw user’s access privileges given by using the GRANT command.

TCL(transaction Control Language): TCL commands deal with the transaction within the database. 

Examples of TCL commands: 

  • COMMIT– commits a Transaction.
  • ROLLBACK– rollbacks a transaction in case of any error occurs.
  • SAVEPOINT–sets a savepoint within a transaction.
  • SET TRANSACTION–specify characteristics for the transaction.

imp terms : Instances, Schema and Sub Schema In DBMS With Examples

click here



SQL vs. MySQL:

  • While SQL is a language used for operating different relational databases, MySQL boasts of being the first open-source relational database in the early 90s. 
  • SQL is a query language, whereas MySQL is a relational database that uses SQL to query a database.
  • You can use SQL to access, update, and manipulate the data stored in a database. However, MySQL is a database that stores the existing data in a database in an organized manner. 
  • SQL is used for writing queries for databases, MySQL facilitates data storing, modifying, and management in a tabular format. 
  • SQL does not have support for any connectors. However, MySQL comes with an integrated tool – MySQL workbench – for designing and building databases. 
  • SQL follows a standard format wherein the basic syntax and commands used for DBMS and RDBMS remain pretty much the same, whereas MySQL receives frequent updates. 
  • SQL supports a single storage engine, but MySQL supports multiple storage engines and also plug-in storage engines. Thus, MySQL is more flexible.
  • In SQL, the server remains independent of the database, which means that you can perform other operations on a database during a data backup session. On the other hand, in MySQL, you can perform a data backup by extract SQL statements. However, unlike SQL, in MySQL, the server blocks the database during a data backup session, minimizing data corruption chances when switching from one MySQL version to another.
  • In terms of data security, the SQL server is much more secure than the MySQL server. In SQL, external processes (like third-party apps) cannot access or manipulate the data directly. While in MySQL, one can easily manipulate or modify the database files during run time using binaries.
  • SQL is not an open-source language. Naturally, if you experience any issue, you cannot expect community support. Instead, you’ll have to rely on Microsoft SQL Server support. Contrary to this, MySQL being an open-source platform, offers rich and robust community support

important keywords

SQL CheatSheet

QUERYDESCRIPTION
DATA MANIPULATION COMMANDS
Select * from <table_name>;To query entire data from the mentioned table
Select col1, col2 from <table_name>;To retrieve two columns from a table
Select col1, col2 from table WHERE condition;To query data from a table based on a condition
Select distinct col from <table_name>;To query distinct records from a table
Select distinct col from table WHERE condition;To query distinct records from a table based on a condition to filter the data
Select col1, col2 from table ORDER BY col2 ASC;To query data from a table and sort the retrieved records in ascending order
Select col1, col2 from table ORDER BY col2 DESC;To query data from a table and sort the retrieved records in descending order
Select aggregate(col1), col2 from table GROUP BY col2;To query data from the table to aggregate the data based on a column.
Select aggregate(col1), col2 from table GROUP BY col2 HAVING condition;To query data from the table to aggregate the data based on a column and a condition.
Select col1, col2 from table1 INNER JOIN table2 ON condition;To query data from multiple tables and inner join the result based on a condition and display a single result
Select col1, col2 from table1 LEFT JOIN table2 ON condition;To query data from multiple tables and left join the result based on a condition and display as a single result
Select col1, col2 from table1 RIGHT JOIN table2 ON condition;To query data from multiple tables and right join the result based on a condition and display as a single result
Select col1, col2 from table1 FULL OUTER JOIN table2 ON condition;To query data from multiple tables and full outer join the result based on a condition and display as a single result
Select col1, col2 from table1 CROSS JOIN table2;To query data from multiple tables and cross join the result to obtain a Cartesian product of the records and display as a single result
Select col1, col2 from table1 UNION select col1, col2 from table2;To query data from multiple tables and combine the results of records from two tables.
DATA DEFINITION COMMANDS
CREATE TABLE table_name (col_name DATA_TYPE CONSTRAINT);To create a table with new columns and all the column definition can be mentioned by a comma
ALTER TABLE table_name;To add a new column to the table
DROP TABLE table_name;To delete the entire table from the database
INSERT INTO table_name(list of columns) VALUES(list of values);To insert data into a table for one record.
UPDATE table SET col1=updated_value;To update a cell value in a table based on column name
UPDATE table SET col1=updated_value1, col2=updated_value2 WHERE condition;To update multiple cell values in a table based on column names and condition
DELETE FROM table_name;To delete the complete data in a table.
DELETE FROM table_name WHERE condition;To delete the complete data in a table based on a condition.
DATA CONTROL COMMANDS
GRANT <Object Privileges> ON <Object Name> TO <user> [GRANT OPTION]To grant access to a particular user based on the grant option and access requirement.
REVOKE <Object Privileges> ON <Object Name> FROM <user>To revoke access to a particular user from a particular object

Free Tips and Tricks of using Cheatsheet SQL Commands

  1. The SQL cheat sheet commands can be used in any IDE or tool where the user has connected to the database using the JAR file of the database type.
  2. The different databases existing in the market are Oracle, Microsoft SQL Server, IBM DB2, etc., which all these can be connected to by using their respective jars and tools to manage the data operations.
  3. There is a different cheat sheet for SQL Aggregate Functions to be used along with the SQL cheat sheet commands to perform some complex operations based on the required data to query as below.
FUNCTIONDESCRIPTION
COUNTTo count the number of elements in a list
SUMTo return the total sum of the elements in a list
MAX
AVGTo return the average of a list
MINTo return the minimum value from a list
MAXTo return the maximum value from a list


Composite Keys












-------------------------------


Comments

Post a Comment

Popular posts from this blog

c++ oops

Image
   class:   It is a user-defined data type, which holds its own data members and member functions, which can be accessed and used by creating an instance of that class. A class is like a blueprint for an object. blue-print representing a group of objects which shares some common properties and behaviours. object: An Object is an instance of a Class. When a class is defined, no memory is allocated but when it is instantiated (i.e. an object is created) memory is allocated. Member Functions in Classes There are 2 ways to define a member function: Inside class definition Outside class definition To define a member function outside the class definition we have to use the scope resolution :: operator along with class name and function name. C++ Constructor: A constructor in C++ is a special ‘MEMBER FUNCTION’ having the same name as that of its class which is used to initialize some valid values to the data members of an object. It is executed automatically whenever an object o...
Read more

Takeoff (hackerearth.3, datastructure, array 1-D)

      There are three planes  A ,  B  and  C . Plane  A  will takeoff on every  p t h  day i.e.  p ,  2 p ,  3 p  and so on. Plane  B  will takeoff on every  q t h  day and plane  C  will takeoff on every  r t h  day. There is only one runway and the takeoff timing is same for each of the three planes on each day. Your task is to find out the maximum number of flights that will successfully takeoff in the period of  N  days. Note:  If there is collision between the flights no flight will take off on that day. Input Format The first line of the input contains a single integer  T , the number of test cases. Then  T  lines follow each containing four space-separated integers  N ,  p ,  q  and  r  as denoted in the statement. Output Format For each test case print a single integer representing the maximum num...
Read more

Aptitude tricks

Image
                                                                                    Time and Work 1.      Work from Days: If A can do a piece of work in  n  days, then A's 1 day's work = 1 . n 2.      Days from Work: If A's 1 day's work = 1 , then A can finish the work in  n  days. n 3.      Ratio: If A is thrice as good a workman as B, then: Ratio of work done by A and B = 3 : 1. Ratio of times taken by A and B to fin...
Read more