MySQL DBA

[vc_row full_width=”stretch_row” css=”.vc_custom_1559286923229{background-color: #f6f6f7 !important;}”][vc_column width=”1/2″][vc_tta_accordion color=”peacoc” active_section=”1″][vc_tta_section title=”What is SQL Server?” tab_id=”1559286383409-ab730398-6c03″][vc_column_text]

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 user, various other applications as well as the database itself. The objective is capturing and analyzing data and manages definition, querying, creation, updating as well as administration of database.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”Compare MySQL Vs. SQL Server” tab_id=”1562740940793-6c70147f-9304″][vc_column_text]

Criteria MySQL SQL Server
Targeted towards Internet servers & Open Source software Corporate & Enterprise market
Functionality Speed Administration, Graphical data modelling
Works with Assumes internet access Administration, Graphical data modelling

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What are the features of MySQL?” tab_id=”1562740940522-cdcd07e8-e823″][vc_column_text]MySQL provides cross-platform support, wide range of interfaces for application programming and has many stored procedures like triggers and cursors that helps in managing the database.

Go through the MySQL Video to get clear understanding of MySQL[/vc_column_text][/vc_tta_section][vc_tta_section title=”What do DDL, DML, and DCL stand for?” tab_id=”1562740940032-18b7f6bf-fc79″][vc_column_text]

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 is CREATE TABLE command. DML denotes Data Manipulation Language such as SELECT, INSERT etc. DCL stands for Data Control Language and includes commands like GRANT, REVOKE etc.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What are the common MySQL functions?” tab_id=”1562740939872-9597acba-5df3″][vc_column_text]Common MySQL functions are as follows: • NOWO – function for returning current date and time as single value. • CURRDATEO – function for returning the current date or time. • CONCAT (X, Y) –function to concatenates two string values creating single string output. • DATEDIFF (X, Y) – function to determine difference two dates.[/vc_column_text][/vc_tta_section][vc_tta_section title=”What are HEAP Tables?” tab_id=”1562740939725-ee9bae79-b1ed”][vc_column_text]

Basically HEAP tables are in-memory and used for high speed temporary storages. But TEXT or BLOB fields are not allowed within them. They also do not support AUTO INCREMENT.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What are the limits for using columns to create the Index?” tab_id=”1562740939538-8bd1f047-e188″][vc_column_text]

The maximum limits of indexed columns that could be created for any table is 16.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”How the user can get the current SQL version?” tab_id=”1562740939386-8f2dc4ee-10b0″][vc_column_text]

The syntax for getting the current version of MySQL is SELECT VERSION ();

[/vc_column_text][/vc_tta_section][vc_tta_section title=” What is the storage engine for MySQL?” tab_id=”1562740939183-583be069-9ee8″][vc_column_text]

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.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What are the different types of tables in MySQL?” tab_id=”1562740939016-7207402a-7d72″][vc_column_text]MyISAM is the default table that is based on the sequential access method.

  • HEAP is the table that is used for fast data access but data will be lost if the table or system crashes. 
  • InoDB is the table that supports transactions using the COMMIT and ROLL BACK commands.
  • BDB can support transactions similar to InoDB but the execution is slower.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What is the use of ENUM in MySQL?” tab_id=”1562740938824-2c52f9ab-5dac”][vc_column_text]

Use of ENUM will limit the values that can go into a table. For instance; the user can create a table giving specific month values and other month values would not enter into the table.

[/vc_column_text][/vc_tta_section][vc_tta_section title=” What is the difference between LIKE and REGEXP operators in MySQL?” tab_id=”1562740938658-ad82c884-027e”][vc_column_text]

LIKE is denoted using the % sign. For example:SELECT * FROM user WHERE user name LIKE “%NAME”.• On the other hand the use of REGEXP is as follows:SELECT * FROM user WHERE username REGEXP “^NAME”;

[/vc_column_text][/vc_tta_section][vc_tta_section title=”How can one take incremental backup in MySQL?” tab_id=”1562740938484-baea11a1-dd19″][vc_column_text]

User can take incremental backup in MySQL using percona xtrabackup.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”How to resolve the problem of data disk that is full?” tab_id=”1562740938295-3d3a1ced-8461″][vc_column_text]

When the data disk is full and overloaded the way out is to create and soft link and move the .frm as well as the .idb files into that link location.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”How to define testing of network layers in MySQL?” tab_id=”1562740938103-92431df0-dec4″][vc_column_text]

For this it is necessary reviewing the layered architecture and determining hardware and software configuration dependencies in respect of the application put to test.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What is the difference between primary key and unique key?” tab_id=”1562740937682-5d0fe90d-c603″][vc_column_text]

While both are used to enforce uniqueness of the column defined but primary key would create a clustered index whereas unique key would create non-clustered index on the column. Primary key does not allow ‘NULL’ but unique key allows it. 

[/vc_column_text][/vc_tta_section][/vc_tta_accordion][/vc_column][vc_column width=”1/2″][vc_tta_accordion color=”peacoc” active_section=”1″][vc_tta_section title=”What is traditional Network Library for the system?” tab_id=”1561382626563-0fa3f678-a5bf”][vc_column_text]

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. Client process needs to know the specific pipe name to send the request.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”How and why use it?” tab_id=”1562740996657-2de8657a-5e10″][vc_column_text]

SQL Server is free and anyone can download and use it. The application uses SQL (Structured Query Language) and is easy to use.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What is the default port for MySQL Server?” tab_id=”1562740998294-6bc75594-3beb”][vc_column_text]The default port for MySQL Server is 3306. Another standard default is port 1433 in TCP/IP for SQL Server.

Learn all about MySQL Server through this MySQl training course.[/vc_column_text][/vc_tta_section][vc_tta_section title=”What are meant by Joins in MySQL?” tab_id=”1562740998963-5ae6088d-c990″][vc_column_text]

In MySQL the Joins are used to query data from two or more tables. The query is made using relationship between certain columns existing in the table. There are four types of Joins in MySQL. Inner Join returns the rows if there is at least one match in both the tables. Left Join returns all the rows form 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 left table. Full Join would return rows when there is at least one match in the tables.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What is the difference between CHAR and VARCHAR?” tab_id=”1562740999725-4e19641b-61a1″][vc_column_text]

When the 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. VARCHAR command is given to adjust the column and table length as required.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What is the syntax for concatenating tables in MySQL?” tab_id=”1562741001808-45189720-68e1″][vc_column_text]

The syntax for concatenating tables is MySQL is CONCAT (string 1, string 2, string 3)

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What are the different types of strings in Database columns in MySQL?” tab_id=”1562741004077-1442daf3-31b8″][vc_column_text]

Different types of strings that can be used for database columns are SET, BLOB, VARCHAR, TEX, ENUM, and CHAR.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”Is there an object oriented version of MySQL library functions?” tab_id=”1562741005222-5d601bf0-efb2″][vc_column_text]

MySQLi is the object oriented version of MySQL and it interfaces in PHP.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What is the difference between primary key and candidate key?” tab_id=”1562741006195-1893a04b-db1b”][vc_column_text]

Primary key in MySQL is use to identify every row of a table in unique manner. For one table there is only one primary key. One of the candidate keys is the primary key and the candidate keys can be used to reference the foreign keys.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”Can you use MySQL with LINUX operating system?” tab_id=”1562741009749-94532152-8009″][vc_column_text]

Yes, the syntax for using MySQL with LINUX operating system is /etc/init.d/mysqlstart

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What are the TRIGGERS that can be used in MySQL tables?” tab_id=”1562741010561-f5073e8a-40b2″][vc_column_text]The following TRIGGERS are allowed in MySQL:• BEFORE INSERT

  • AFTER INSERT
  • BEFORE UPDATE
  •  AFTER UPDATE
  •  BEFORE DELETE
  •  AFTER DELETE

[/vc_column_text][/vc_tta_section][vc_tta_section title=”How to use the MySQL slow query log?” tab_id=”1562741011374-adba344c-12c6″][vc_column_text]

Information that is provided on the slow query log could be huge in size. The query could also be listed over thousand times. In order to summarize the slow query log in an informative manner one can use the third party tool “pt-qury-digest”.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”How can you change the root password if the root password is lost?” tab_id=”1562741012360-526056ec-72d4″][vc_column_text]

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 normal mode.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What is the difference between BLOB and TEXT?” tab_id=”1562741013927-343b1ee2-85d2″][vc_column_text]

BLOBs are binary large object holding huge data. 4 types of BLOB are TINYBLOB, BLOB, MEDIBLOB, and LONGBLOB. TEXT is case-sensitive BLOB. 4 types of TEXT are TINY TEXT, TEXT, MEDIUMTEXT, and LONG TEXT.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”How can one restart SQL Server in single user or minimal configuration modes?” tab_id=”1562741015065-4d2d3602-d45e”][vc_column_text]

The command line SQLSERVER.EXE used with –m will restart SQL Server in single user mode and with –f will start it in minimal configuration mode.

[/vc_column_text][/vc_tta_section][vc_tta_section title=”What is meant by transaction and ACID properties?” tab_id=”1562741016461-b6123435-4806″][vc_column_text]

Transaction is 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.

[/vc_column_text][/vc_tta_section][/vc_tta_accordion][/vc_column][/vc_row]

WhatsApp us