SQL Server is one of the database management systems (DBMS) and is designed by Microsoft. DBMS are computer software applications with the capability of interacting with users, various other applications, and databases. The objective of SQL Server is capturing and analyzing data and managing the definition, querying, creation, updating, and administration of the database.
Learn for free ! Subscribe to our youtube Channel.
SQL Server is free and anyone can download and use it. The application uses SQL (Structured Query Language), and it is easy to use.
MySQL provides cross-platform support, a wide range of interfaces for application programming, and has many stored procedures like triggers and cursors that help in managing the database.
In either Windows or POSIX systems, the named pipes provide ways of inter-process communications to connect different processes running on the same machine. It dispenses with the necessity of using the network stack, and data can be sent without affecting the performance. Servers set up named pipes to listen to requests. The client process needs to know the specific pipe name to send the request.
The default port for MySQL Server is 3306. Another standard default port is 1433 in TCP/IP for SQL Server.
Learn all about SQL through this SQL Certification Course!
DDL is the abbreviation for Data Definition Language dealing with database schemas, as well as the description of how data resides in the database. An example of this is the CREATE TABLE command. DML denotes Data Manipulation Language which includes commands such as SELECT, INSERT, etc. DCL stands for Data Control Language and includes commands like GRANT, REVOKE, etc.
In MySQL, joins are used to query data from two or more tables. The query is made using the relationship between certain columns existing in the table. There are four types of joins in MySQL. Inner join returns rows if there is at least one match in both tables. Left join returns all the rows from the left table even if there is no match in the right table. Right join returns all the rows from the right table even if no matches exist in the left table. Full join would return rows when there is at least one match in the tables.
Master SQL concepts in depth from this SQL Tutorial now!
Common MySQL functions are as follows:
Check out Intellipaat’s blog to get a fair understanding of SQL Optimization Techniques!
When a table is created, CHAR is used to define the fixed length of the table and columns. The length value could be in the range of 1–255. The VARCHAR command is used to adjust the column and table lengths as required.
Basically, Heap tables are in-memory tables used for high-speed temporary storage. But, TEXT or BLOB fields are not allowed within them. They also do not support AUTO INCREMENT.
The syntax for concatenating tables is MySQL:
CONCAT (string 1, string 2, string 3)
The maximum limit of indexed columns that can be created for any table is 16.
In MySQL, the different types of strings that can be used for database columns are SET, BLOB, VARCHAR, TEXT, ENUM, and CHAR.
The syntax for getting the current version of MySQL:
SELECT VERSION ();
Yes. MySQLi is the object-oriented version of MySQL, and it interfaces in PHP.
Storage tables are named as table types. The data is stored in the files using multiple techniques such as indexing, locking levels, capabilities, and functions.
The primary key in MySQL is used to identify every row of a table in a unique manner. For one table, there is only one primary key. The candidate keys can be used to reference the foreign keys. One of the candidate keys is the primary key.
Want to get certified in SQL? Go through our blog on SQL Server Certification and be informed!
MyISAM is the default table that is based on the sequential access method.
Yes. The syntax for using MySQL with Linux operating system is as follows:
The use of ENUM will limit the values that can go into a table. For instance, a user can create a table giving specific month values and other month values would not enter into the table.
Following TRIGGERS are allowed in MySQL:
SELECT * FROM user WHERE user name LIKE “%NAME”
SELECT * FROM user WHERE username REGEXP “^NAME”;
Information that is provided on the slow query log could be huge in size. The query could also be listed over a thousand times. In order to summarize the slow query log in an informative manner, one can use the third-party tool ‘pt-query-digest’.
A user can take an incremental backup in MySQL using Percona XtraBackup.
In such cases when the password is lost, the user should start the DB with skip-grants-table and then change the password. Thereafter, with the new password, the user should restart the DB in a normal mode.
When the data disk is full and overloaded, the way out is to create and soft link and move the .frm and the .idb files into that link location.
Basically, DELETE TABLE is a logged operation, and every row deleted is logged. Therefore, the process is usually slow. TRUNCATE TABLE also deletes rows in a table, but it will not log any of the rows deleted. The process is faster here in comparison. TRUNCATE TABLE can be rolled back and is functionally similar to the DELETE statement without a WHERE clause.
There are four types of joins in MySQL. Inner join returns the rows if there is at least one match in two tables. Left join returns all the rows from the left table even if there is no match in the right table. Right join returns all the rows from the right table even if no matches exist in the left table. Full join would return rows when there is at least one match in the tables.
The storage models in OLAP are MOLAP, ROLAP, and HOLAP.
For this, it is necessary to review the layered architecture and determine hardware and software configuration dependencies with respect to the application put to test.
While both are used to enforce the uniqueness of the column defined, the primary key would create a clustered index, whereas the unique key would create a non-clustered index on the column. The primary key does not allow ‘NULL’, but the unique key does.
Transaction is a logical unit of work where either all or none of the steps should be performed. ACID is the abbreviation for Atomicity, Consistency, Isolation, and Durability that are properties of any transaction.
The command line SQLSERVER.EXE used with ‘–m’ will restart SQL Server in the single user mode and the same with ‘–f’ will restart it in the minimal configuration mode.
BLOBs are binary large object holding huge data. Four types of BLOBs are TINYBLOB, BLOB, MEDIBLOB, and LONGBLOB. TEXT is a case-sensitive BLOB. Four types of TEXT are TINY TEXT, TEXT, MEDIUMTEXT, and LONG TEXT.
If you have any doubts or queries related to SQL, get them clarified from SQL experts on our SQL Community!
The logical architecture of MySQL is made of ‘connection manager’, ‘query optimizer’, and ‘pluggable engines’.
There are various advantages and disadvantages of using MySQL. Some of them are given below:
Besides, some of the features that make MySQL unique are deadlock identification, execution of multiple transactions, efficient processing, and easy management.
Timestamp in SQL Server helps in row versioning. Row versioning is a type of concurrency that allows retaining the value until it is committed in the database. It shows the instant time of any event. It consists of both the date and time of the event. Also, timestamp helps in backing up data during the failure of a transaction.
While we insert, update, or delete a record, the date and time automatically get inserted.
As the name suggests, mysqldump is used to dump one or more created databases. It performs backups for data or transfers the data from SQL Server to another. Also, it helps in producing the initial database schema by logical backups. Moreover, unlike triggers, mysqldump does not backup the stored procedures or functions by default.
mysqldump [options] db_name [tables]
mysqldump [options] –databases db1 [db2 db3...]
mysqldump [options] –all-databases
Every organization has some crucial data specific to its business. This data needs secure access so that any consequence due to data loss can be avoided. For this, organizations create a sequence of permissions that are linked to various data objects. These lists are known as the access control list (ACL).
ACL serves the basis for the server’s security that helps troubleshoot the connection problems for users. These are also known as grant tables that are cached by MySQL. MySQL verifies a user for authentication and grants permissions in a sequence when the user executes a command.
extually i am very help to this sql concept . to read this i know cleared interview. thank a lot intellipaat . i think everyone shuld learn this.
Your email address will not be published. Required fields are marked *
Solve : * 7 + 24 =