09. SQL Commands Part 1
Ans: RDBMS stands for Relational Database Management System.
Ans: Any meaningful information is known as Data. Data is a collection of raw facts, figures, and symbols that can be processed and analyzed to provide meaningful information.
Ans: DB stands for Database. A place where data is stored is Database. There are two types of DB.
- Structured Database: It means when you store data in rows and column format. It is a fixed format.
- Unstructured Database: Where we store data in raw format. Like picture, videos, paragraph, etc.
Ans: Any application can be called RDBMS, if they follow all 12 CODD's rule:-
Rule 1: The Information Rule All information, whether it is user information or metadata, that is stored in a database must be entered as a value in a cell of a table. It is said that everything within the database is organized in a table layout.
Rule 2: The Guaranteed Access Rule Each data element is guaranteed to be accessible logically with a combination of the table name, primary key (row value), and attribute name (column value).
Rule 3: Systematic Treatment of NULL Values Every Null value in a database must be given a systematic and uniform treatment.
Rule 4: Active Online Catalog Rule The database catalog, which contains metadata about the database, must be stored and accessed using the same relational database management system.
Rule 5: The Comprehensive Data Sublanguage Rule A crucial component of any efficient database system is its ability to offer an easily understandable data manipulation language (DML) that facilitates defining, querying, and modifying information within the database.
Rule 6: The View Updating Rule All views that are theoretically updatable must also be updatable by the system.
Rule 7: High-level Insert, Update, and Delete A successful database system must possess the feature of facilitating high-level insertions, updates, and deletions that can grant users the ability to conduct these operations with ease through a single query.
Rule 8: Physical Data Independence Application programs and activities should remain unaffected when changes are made to the physical storage structures or methods.
Rule 9: Logical Data Independence Application programs and activities should remain unaffected when changes are made to the logical structure of the data, such as adding or modifying tables.
Rule 10: Integrity Independence Integrity constraints should be specified separately from application programs and stored in the catalog. They should be automatically enforced by the database system.
Rule 11: Distribution Independence The distribution of data across multiple locations should be invisible to users, and the database system should handle the distribution transparently.
Rule 12: Non-Subversion Rule If the interface of the system is providing access to low-level records, then the interface must not be able to damage the system and bypass security and integrity constraints.
Ans: A few example of DB are Foxford, Dbase, etc.
[Note: You cannot handle multiple objects in DBMS, so that's why in 1995 RDBMS technology was developed by 2000 all DBMS were developed to RDBMS. when we relate multiple objects in a application or queries, i.e., where we get response from multiple objects.]
Ans: Every column is known as fields and every row are known as records.
Ans: Oracle, My SQL Server, MSSQL, DB2, Sybase, Postgress.
[Note: Remember MongoDB is not a RDBMS, it's a NoSQL Database.]
Ans: Anything that we do in a DB is known as transaction. Like inserting, storing, updating value in a DB that is known as transaction. Every transaction in a DB must follow a property called ACID.
Ans:
It means if a transaction begins it must reach till the end, in between if it is interrupted it's not a be treated as a transaction. For example, in a online transaction I am transferring some money from my account to another person. First we go to my online transaction app, we login, then select the amount, then we either give mpin or otp. If this process is interrupted somehow then we have to start over again.
It means that total volume remains the same. For example, if I have a photo of 10mb it must remain 10mb after the photo is transferred to DB. So before and after the transaction the total volume of Data must remain the same. Another example is if I have a total amount of 1000 in my bank account and I withdraw Rs 500 from it. The remaining balance must be Rs 500 not 600 or anything else.
It states that transactions don't affect each other which means every transaction process is parallel to each other. For example, in a train ticket booking system millions of transaction or ticket booking happens in 1 minute, do they affect each other, no. No transactions gets mixed up because every process is parallel to each other.
It states that data will remain as it is unless and until someone edits that data. For example, if a employee joins a new company he will get the salary that is registered in the DB. His salary won't increase suddenly from the next month unless and until the administrators changes the salary amount for the said employee.
Ans: We interact with RDBMS through SQL language. SQL stands for Structured Query Language.
Ans: There are 5 types of SQL commands:-
- Data Definition Language (DDL)- Any statement that starts with CREATE, ALTER, DROP,TRUNCATE.
- Data Manipulation Language (DML)- Any statement that starts with INSERT, UPDATE, DELETE.
- Data Query Language (DQL)- Any statement that starts with SELECT.
- Data Control Language (DCL)- Any statement that starts with GRANT, REVOKE.
- Transaction Control Language (TCL)- Any statement that starts with COMMIT, ROLLBACK & SAVEPOINT.
[Note: Sqlplus is a tool where we will use SQL statement.]
code: cat /etc/oratab
It means in this serve we have two database orcl and prod.
code: ps -ef | grep pmon
It means that from the list of process we are searching the word "pmon". "pmon" means process monitor. If the code doesn't show any result means that no database is running.
code: . oranev
Then give the DB name. If this shows then everything is going well.
Ans: First set up the environment for DB, then give DB name then follow the code code: sqlplus hr/oracle
[Note: hr is username, oracle is password]
code: ! or ho
code: exit
code: exit
code: sqlplus hr/oracle
[Note: SQL is not entirely case sensitive.]
code: show user
code: clear screen or cle scr
