SQL Fundamentals: A Comprehensive Guide to Database Management | Cheat Sheet Programming Languages

 What is SQL?

SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a

database, to create a table in the database, to retrieve data or update a table in the database, etc.

Using SQL, we can do many things. For example – we can execute queries, we can insert records into a table,

can update records, can create a database, can create a table, can delete a table, etc.

 What is a database

database is a structured collecon of data stored and organized for easy access, retrieval, and management.

Databases are designed to store large amounts of informaon, typically in tables that consist of rows and

columns, making it simple to organize, query, and manipulate data. SQL (Structured Query Language) is the

language used to interact with and manage data within these databases.



Does SQL support programming language features?

While SQL is indeed a language, it is a command language rather than a full-ﬂedged programming language,

as it doesn’t support tradional programming constructs like loops or condional statements (e.g., `for`

loops or `if...else`). Instead, SQL provides a set of commands that enable us to query, update, delete, and

manipulate data within a database. It is speciﬁcally designed for managing and interacng with structured

data rather than for general-purpose programming tasks.



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

Both of these data types are used for characters, but varchar2 is used for character strings of variable length,

whereas char is used for character strings of ﬁxed length.

For example, if we specify the type as char(5) then we will not be allowed to store a string of any other length

in this variable, but if we specify the type of this variable as varchar2(5) then we will be allowed to store strings

of variable length. We can store a string of length 3 or 4 or 2 in this variable.

 What d o you mean by data deﬁnion language

Data deﬁnion language or DDL allows to execuon of queries like CREATE, DROP and ALTER. That is those

queries that deﬁne the data.



What do you mean by data manipulation language?

Data manipulaon Language or DML is used to access or manipulate data in the database. It allows us to

perform the below-listed funcons:

 Insert data or rows in a database

 Delete data from the database

 Retrieve or fetch data

 Update data in a database

What is a view in sql:

Views in SQL are a kind of virtual table. A view also has rows and columns as they are on a real table in the database.

We can create a view by selecng ﬁelds from one or more tables present in the database. A View can either have all

the rows of a table or speciﬁc rows based on certain condions. The CREATE VIEW statement of SQL is used for creang

views.

Partial preview of the text

Download SQL Fundamentals: A Comprehensive Guide to Database Management and more Cheat Sheet Programming Languages in PDF only on Docsity!

 What is SQL? SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database, etc. Using SQL, we can do many things. For example – we can execute queries, we can insert records into a table, can update records, can create a database, can create a table, can delete a table, etc.  What is a database database is a structured collecƟon of data stored and organized for easy access, retrieval, and management. Databases are designed to store large amounts of informaƟon, typically in tables that consist of rows and columns, making it simple to organize, query, and manipulate data. SQL (Structured Query Language) is the language used to interact with and manage data within these databases.  Does SQL support programming language features? While SQL is indeed a language, it is a command language rather than a full-fledged programming language, as it doesn’t support tradiƟonal programming constructs like loops or condiƟonal statements (e.g., for loops or if...else). Instead, SQL provides a set of commands that enable us to query, update, delete, and manipulate data within a database. It is specifically designed for managing and interacƟng with structured data rather than for general-purpose programming tasks.  What is the difference between CHAR and VARCHAR2 datatype in SQL? Both of these data types are used for characters, but varchar2 is used for character strings of variable length, whereas char is used for character strings of fixed length. For example, if we specify the type as char(5) then we will not be allowed to store a string of any other length in this variable, but if we specify the type of this variable as varchar2(5) then we will be allowed to store strings of variable length. We can store a string of length 3 or 4 or 2 in this variable.  What d o you mean by data definiƟon language Data definiƟon language or DDL allows to execuƟon of queries like CREATE, DROP and ALTER. That is those queries that define the data.  What do you mean by data manipulation language? Data manipulaƟon Language or DML is used to access or manipulate data in the database. It allows us to perform the below-listed funcƟons:  Insert data or rows in a database  Delete data from the database  Retrieve or fetch data  Update data in a database What is a view in sql: Views in SQL are a kind of virtual table. A view also has rows and columns as they are on a real table in the database. We can create a view by selecƟng fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain condiƟons. The CREATE VIEW statement of SQL is used for creaƟng views.

 What is a foreign key? A foreign key is a field (or a set of fields) in one table that uniquely identifies a row in another table, creating a relationship between the two tables. It is used to maintain referential integrity within a database by ensuring that the values in the foreign key field match a primary key value in the referenced table.  What are table and field?  What is the primary key? A primary key is a unique idenƟfier for each row in a table within a database. It ensures that each record in the table is disƟnct and can be uniquely idenƟfied. A primary key must contain unique values and cannot have NULL values, meaning every row must have a valid and unique primary key value.  What is a Default constraint? A default constraint in SQL specifies a default value for a column when a new row is added without explicitly assigning a value to that column. This helps ensure that columns have meaningful values even if no data is provided during an insert operaƟon.  What is a query? An SQL query is used to retrieve the required data from the database.  What is a subquery? In SQL, a Subquery can be simply defined as a query within another query. In other words, we can say that a Subquery is a query that is embedded in the WHERE clause of another SQL query.  What are the different operators available in SQL? There are three operators available in SQL namely:

ArithmeƟc Operators
Logical Operators
Comparison Operators  What is a constraint? Constraints are the rules that we can apply to the type of data in a table. we can specify the limit on the type of data that can be stored in a parƟcular column in a table using constraints.  What is auto increment? Auto Increment is a feature used in SQL to automaƟcally generate a unique value for a column, typically for the primary key, every Ɵme a new row is inserted into a table. This ensures that each record has a unique idenƟfier without requiring the user to manually specify the value for that column.

 What is ETL in SQL? ETL stands for Extract, Transform, Load, and it refers to a process in database management, parƟcularly in data warehousing, that involves extracƟng data from various sources, transforming it into a suitable format, and then loading it into a desƟnaƟon system (usually a data warehouse or database). The goal of ETL is to integrate data from mulƟple sources, clean and transform it for analysis, and store it in a central repository.  What is SQL injecƟon?  SQL injecƟon is a code injecƟon technique that might destroy your database.  SQL injecƟon is one of the most common web hacking techniques.  SQL injecƟon is the placement of malicious code in SQL statements, via web page input.  What is a trigger and how can it be disabled? A trigger in SQL is a type of stored procedure that is automaƟcally executed or triggered in response to a specific event or acƟon that occurs on a database table or view. Triggers are used to enforce business rules, data integrity, and automate certain tasks without requiring explicit user intervenƟon. Disabling a trigger temporarily prevents it from execuƟng during insert, update, or delete operaƟons, depending on the type of trigger. Disabling triggers can be useful during maintenance, tesƟng, or troubleshooƟng. DROP TRIGGER IF EXISTS trigger_name;  Difference between sql and pl/sql

 What is the difference between BETWEEN and IN operators in SQL?  What is the difference between primary key and unique constraints? The primary key cannot have NULL values, the unique constraints can have NULL values. There is only one primary key in a table, but there can be mulƟple unique constraints. The primary key creates the clustered index automaƟcally but the unique key does not.  What is a join in SQL? What are the types of joins? A JOIN in SQL is a technique used to combine rows from two or more tables based on a related column between them. Joins allow you to retrieve data that is spread across mulƟple tables.

 Explain WITH clause in SQL? The WITH clause in SQL is a powerful tool that allows you to define Common Table Expressions (CTEs). The WITH clause provides a way relaƟonship of defining a temporary relaƟonship whose definiƟon is available only to the query in which the with clause occurs. ‘  What is the difference between SQL DELETE and SQL TRUNCATE commands?  What is Case WHEN in SQL? The CASE WHEN statement in SQL is used for condiƟonal logic within SQL queries. It allows you to add IF-THEN-ELSE logic, enabling different outcomes based on certain condiƟons within your query. You can use CASE WHEN for condiƟonal column values, calculated fields, sorƟng, or filtering.  Name different types of case manipulaƟon funcƟons available in SQL. There are three types of case manipulaƟon funcƟons available in SQL. They are,  LOWER: The purpose of this funcƟon is to return the string in lowercase. It takes a string as an argument and returns the string by converƟng it into lower case. Lower(‘string’)  UPPER: The purpose of this funcƟon is to return the string in uppercase. It takes a string as an argument and returns the string by converƟng it into uppercase. Syntax: UPPER(‘string’)  INITCAP: The purpose of this funcƟon is to return the string with the first leƩer in uppercase and the rest of the leƩers in lowercase. Syntax: INITCAP(‘string’)

 difference between drop and truncate:  Explain SQL HAVING statement. The HAVING statement in SQL is used to filter the results of aggregate funcƟons (such as SUM, COUNT, AVG, MAX, and MIN) aŌer a GROUP BY clause. It allows you to specify condiƟons for groups of rows, unlike the WHERE clause, which filters individual rows before aggregaƟon.

What is the difference between COALESCE() & ISNULL()? COALESCE(): COALESCE funcƟon in SQL returns the first non-NULL expression among its arguments. If all the expressions evaluate to null, then the COALESCE funcƟon will returnsnull. Syntax: SELECT column(s), CAOLESCE(expression_1,….,expression_n)FROM table_name; ISNULL(): The ISNULL funcƟon has different uses in SQL Server and MySQL. In SQL Server, ISNULL() funcƟon is used to replace NULL values. Syntax: SELECT column(s), ISNULL(column_name, value_to_replace)FROM table_name;

SQL Fundamentals: A Comprehensive Guide to Database Management, Cheat Sheet of Programming Languages

Related documents

Partial preview of the text

Download SQL Fundamentals: A Comprehensive Guide to Database Management and more Cheat Sheet Programming Languages in PDF only on Docsity!