Top 20 MySQL Interview Questions & Answers

  

Ques: 1). I keep getting an error about a foreign key constraint failing when I run the DELETE statement. So, what do I do now?

Answer:

This suggests that some of the data you're attempting to delete is still present in another table. For example, if you have a table for universities and a table for students, both of which contain the ID of the university they attend, deleting a university table will fail if the students table still contains people who are enrolled at that university. The proper procedure is to delete the offending data first, followed by the university in question. Running SET foreign key checks=0 before the DELETE operation and then adjusting the parameter back to 1 after the DELETE command would be a quick way.

 

Ques: 2). You created a search engine that should provide ten results at a time, but you also want to know how many rows there are in total. How are you going to show that to the user?

Answer:

SELECT page title FROM web pages FROM SQL CALC FOUND ROWS LIMIT 1,10; 

SELECT FOUND ROWS(); 

The second query will tell you how many results there are (not that COUNT() is never utilised). the total, so you can put something like "Found 13,450,600 results, displaying 1-10" on the screen. It's worth noting that FOUND ROWS ignores the LIMITS you set and always returns the whole number of rows affected by the query.

 

Ques: 3). Differentiate between MyISAM Static and MyISAM Dynamic.

Answer:

All fields in MyISAM static have a defined width. To handle data types of varied lengths, the Dynamic MyISAM table would have fields such as TEXT, BLOB, and so on. In the event of corruption, MyISAM Static would be easier to restore since, even if you lose some data, you know exactly where to search for the beginning of the next record.

 

Ques: 4). What are the benefits of MyISAM versus InnoDB?

Answer:

Much more cautious disc space management - each MyISAM table is saved in its own file, which may subsequently be compressed using myisamchk if necessary. Tables in InnoDB are kept in tablespace, and there isn't much room for further optimization. Except for TEXT and BLOB, all data can only take up 8,000 bytes. InnoDB does not support full text indexing. Due to tablespace complexity, TRhe COUNT(*)s run slower than in MyISAM.

 

Ques: 5). What are MySQL's HEAP tables?

Answer:

The HEAP tables are stored in memory. They're typically utilised for high-speed, short-term storage. Within HEAP tables, no TEXT or BLOB fields are permitted. Only the comparison operators = and => can be used. AUTO INCREMENT is not supported by HEAP tables. The indexes must not be NULL.

 

Ques: 6). Difference between primary key, unique key and candidate key.

Answer:

Primary Key:- (i) It has unique value and it can’t accept null values.

(ii) We can have only one Primary key in a table.

Unique Key:- (i) It has unique value and it can accept only one null values.

(ii) We can have more than one unique key in a table.

Candidate Key:- candidate key full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key. So thus type of key is known as candidate key. Every table must have at least one candidate key but at the same time can have several.

 

Ques: 7). What is the Difference Between MySQL and MySQL AB?

Answer:

The term "MySQL" is used to refer to both the MySQL database management system and the corporation that created it. MySQL AB is the full name of the firm. MySQL is the database management system (DBMS) that MySQL AB owns, develops, and sells—that is, the "MySQL" database server software and related items such client applications for talking with the server and programming interfaces for creating new clients.

The "AB" in the company name is an abbreviation for the Swedish "aktiebolag," or "stock company." As a result, the company's name is "MySQL Inc." MySQL Inc. and MySQL GmbH, for example, are subsidiaries of MySQL AB.

 

Ques: 8). What are the features of MYSQL?

Answer:

It's a pretty effective programme on its own. It can handle a significant portion of the features found in the most expensive and sophisticated database solutions.

• It makes use of a standard version of the widely used SQL data language.
• It's available under a free and open-source licence.
• It is compatible with a wide range of operating systems and languages.
• It operates swiftly and efficiently, even when dealing with massive data sets.
• PHP has a variety of functions for working with MySQL databases.

 

Ques: 9). What are the steps you take in phpMyAdmin to modify a table?

Answer:

From the table list on the left side of the SQL screen, choose the table you want. In the main area of the screen, the table shows in a spreadsheet-like manner. In this window, we can change the table's contents.

By clicking the appropriate button displayed near the record, you can edit or remove it.

By clicking the corresponding link near the bottom of the table, you can add a row.

Press the Enter key to exit the cell you just edited. Any modifications you make to the data in the table are automatically transformed to SQL code.

 

Ques: 10). What are your thoughts on TIMESTAMP?

Answer:

The attributes DEFAULT CURRENT TIMESTAMP and ON UPDATE CURRENT TIMESTAMP of the TIMESTAMP data type enable for automated updating. Each table can only have one automatically updated TIMESTAMP field. That is, only one TIMESTAMP field with DEFAULT CURRENT TIMESTAMP or ON UPDATE CURRENT TIMESTAMP can be defined. On a single field, one or both attributes can be given.

 

Ques: 11). Is the data type DATETIME a STRING?

Answer:

DATETIME data types are commonly formatted as strings, and they can also be inputted as strings. They do, however, have numerical representations, which are visible when the value is cast into a numeric data type.

 

Ques: 12). What is the maximum number of privilege levels that can be granted?

Answer:

Privileges can be issued at five different levels.

Global - Privileges that apply to all databases on a MySQL server are known as global privileges. The mysql.user table stores these privileges.

Database - Database rights are applied to all items in a given database. The mysql.db and mysql.host tables store these privileges.

Table  - Table privileges are applied to all columns in a table. The mysql.tables priv table stores these privileges. Only table level rights are granted and revoked using the GRANT ALL ON db name.table name and REVOKE ALL ON db name.table name commands.

Column - Privileges at the column level apply to one or more columns in a table. The mysql.columns priv table stores these privileges. You must specify the same columns that were given when using the REVOKE command to remove column level access. Within parenthesis, type the column or columns for which privileges are to be provided.

Routine - For stored routines, the privileges CREATE ROUTINE, ALTER ROUTINE, EXECUTE, and GRANT apply (functions and procedures). At the global and database levels, they can be granted. These privileges can also be provided at the routine level for individual routines, with the exception of CREATE ROUTINE. The privileges are kept in the table mysql.procs priv.

 

Ques: 13). What exactly is SQLyog?

Answer:

SQLyog is perhaps the most widely used GUI tool for MySQL. The programme has been around since August 2002, and we can see how mature it is when we utilise it. This can be a very useful and practical tool for folks who come from a Windows desktop experience. There is a free community version that is somewhat limited, however it only operates on Windows desktops.

 

Ques: 14). Tell me how you back up a MYSQL database?

Answer:

With phpMyAdmin, backing up databases is simple. By clicking the database name in the left-hand navigation bar, we can choose the database we wish to back up. After that, we select the Export button and make sure that all of the tables we want to back up are highlighted.

Then, under Export, we can define the choices we desire. Make sure we can save the output by entering a filename.

 

Ques: 15). What are the differences between the heap table & temporary table?

Answer:

Heap Table:

• Found in memory. It works as storage temporarily.
• BLOB & TEXT fields aren’t allowed
• Indexes should be “NOT NULL”
• Doesn’t supports “AUTO_INCREMENT”
• Can be shared among clients
• Only comparison operators can be used (=,<,>,>=, <=)

Temporary Table:

•  Used to store provisional data
• Temporarily stored data is deleted after client session ends
• Aren’t shared among clients
• Special syntax is used; “create temporary table”

 

Ques: 16). Why would you want to utilise the MySQL Database Server?

Answer:

The MySQL Database Server is a fast, dependable, and simple to use database server. Anyone with access to the internet can use and alter the software. Anyone can use MySQL for free by downloading it from the Internet.

MySQL Database Software is a client/server system that includes a multi-threaded SQL server that supports a variety of backends, a variety of client programmes and libraries, administrative tools, and a variety of application programming interfaces (APIs).

 

Ques: 17). Is the phpMyAdmin interface user-friendly?

Answer:

The UI of the phpMyAdmin application is fairly user-friendly. Many administrators are at least somewhat familiar with it because it is utilised by a number of web hosting companies. Although it has a limited set of capabilities, it does offer some flexibility in that we can use a web browser to view the application from anywhere.

 

Ques: 18). What are the Common MYSQL Function?

Answer:

CONCAT(A, B) - Concatenates two string values to create a single string output. Often used to combine two or more fields into one

FORMAT(X, D) - Formats the number X to D significant digits.

CURRDATE(), CURRTIME() - Returns the current date or time.

NOW() - Returns the current date and time as one value.

MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() - Extracts the given data from a date value.

HOUR(), MINUTE(), SECOND() - Extracts the given data from a time value.

DATEDIFF(A, B) - Determines the difference between two dates-commonly used to calculate ages

SUBTIMES(A, B) - Determines the difference between two times.

FROMDAYS(INT) - Converts an integer number of days into a date value.

 

Ques: 19). What are Access Control Lists and how do they work?

Answer:

An Access Control List (ACL) is a set of permissions associated with a certain item. This list is the foundation of the MySQL server's security paradigm, and knowing it can immensely assist you in diagnosing problems with users being unable to connect.

The ACLs (also known as grant tables) are cached in memory by MySQL. MySQL validates the authentication information and permissions against the ACLs in a predetermined order when a user tries to authenticate or run a command.

 

Ques: 20). What is the "i-am-a-dummy flag" in MySQL used for?

Answer:

When we want to deny or suspend "UPDATE & DELETE" instructions unless there is a WHERE clause present, we utilise the "i-am-a-dummy" flag.

Top 20 Oracle Cloud Interview Questions & Answers

Ques: 1. What are the database related public cloud offerings in oracle cloud?

Answer:

Oracle public cloud offering comes with three database related services in the public cloud. They are as follows:

a) Schema as a Service (SAAS).

b) Database as a service (DAAS)– Also known as DBaaS, this public cloud is offered as both Infrastructure as a service IAAS and Platform as a Service PAAS.

c) Oracle database cloud Ex-Data service.

 

Oracle Fusion Applications interview Questions and Answers

 

Ques: 2. How will you provision an oracle database?

Answer:

As an oracle cloud dba, we collect requirements from customers and create it as per given requirements. In case your project needs a clone of production database immediately for testing and UAT purpose. for example, it can be done as per standard specification requirements often based on a template.

 

Oracle Accounts Payables Interview Questions and Answers

 

Ques: 3. Why should I choose oracle cloud over others, as there are many different cloud services available in market?
 

Answer:

Oracle cloud offers database as a service. Oracle database is the popular relational database company that has been in existence so long. With oracle databases supporting enterprise databases, this is a cloud service that can support databases that are used for testing, development, all the way up to live production databases. Oracle cloud offers high availability, scalability options offering business continuity, time save and lot more advantages. A cloud service from a database company to keep it simple.

 

Oracle ADF Interview Questions and Answers                                 

 

Ques: 4. what database management services are included in oracle cloud platform as service offering?

Answer:

Following database management services are included as part of Oracle cloud platform as a service PAAS: 

• Database
• Database backup 
• Big Data
• Big Data Cloud 
• Event Hub 
• MySQL 
• Autonomous NoSQL Database Cloud 
• Data hub
• Autonomous Data Warehouse Cloud.

 

Oracle Access Manager Interview Questions and Answers

 

Ques: 5. Where can you store your oracle database backup in oracle cloud?
 

Answer:

Oracle cloud objects storage solution that is reliable and scalable. As of information from oracle website this is a 8000TB storage that is used for storing and accessing the data in databases that are ever growing. This is storage used for storing oracle database backup data.
 

Oracle Fusion HCM Interview Questions and Answers

 

Ques: 6. What are the unique features of oracle cloud database backup management offering?
 

Answer:

This is a reliable and scalable object data storage from oracle cloud. Some unique features includes:

• Security – This solution comes with enterprise-grade data protection policies. The privacy policies are in enterprise grade as well. Oracle being the most popular enterprise database solution this cloud offerings from oracle are designed. 
• Reliability – Redundancy policies ensure high availability of data. 
• Scalability – Oracle cloud is a pay as you go model solution wherein you can choose to purchase universal credits, BYOL etc. Based on capacity and growing demands storage hardware is allocated making this a scalable solution. 
• Simplification by using existing RMAN for backups – Even with cloud oracle is still making use of RMAN backup for performing the database backups. This makes backup, restore and recovery operations transparent using RMAN.

 

Oracle SCM Interview Questions and Answers

 

Ques: 7. What is the difference between normal database and Oracle database cloud service?
 

Answer:

Oracle database cloud service is same as single-instance oracle database except for that database is deployed in cloud and computing resources including storage, power etc is provided by Oracle.

 

Oracle Financials Interview questions and Answers

 

Ques: 8. What are the two service levels available with oracle database cloud service?
 

Answer:

Oracle Database Cloud Service Virtual image wherein customer is responsible for installing software, maintenance of software. Customer has root privilege and full database administrative privilege.
 

Oracle Database Cloud Service wherein database deployment is easy using custom options provided online. Oracle database cloud service can perform automated backups. Customer is responsible for setting up maintenance operations, recovery operation setup in the event of failure.

Oracle PL/SQL Interview Questions and Answers

 

Ques: 9. Which component of Oracle cloud provides service console and REST API?
 

Answer:

Platform Service Manager the PSM component of Oracle cloud is responsible for this. This component is the one used in Oracle golden gate and Oracle Java cloud service as well.

 

Oracle SQL Interview Questions and Answers

 

Ques: 10. How does PSM interact with compute nodes to perform predefined cloud service actions like backup, patching?
 

Answer:

PSM uses secure shell the SSH in port 22 of compute nodes. Compute nodes hosts databases which as database deployments in oracle cloud terms. These cations can be initiated over web service console that uses PSM or REST API.

 

Oracle RDMS Interview Questions and Answers

 

Ques: 11. How will you achieve high availability in Oracle Cloud Infrastructure?
 

Answer:

Attach block volume from availability domain 1 to a compute instance in availability domain 2 or vice versa. Distribute application servers across all availability domains within a region.

BI Publisher Interview Questions and Answers

 

Ques: 12. What are the components of backend set of a load balancer?

 

Answer:

a) Load balancing policy
b) list of backend servers
c) health check policy
d) SSL handling
e) session persistence configuration

Oracle 10g Interview Questions and Answers

 

Ques: 13. What are the many different database deployment model available with oracle cloud?
 

Answer:

Following are the many different types of database deployment models that come as part of oracle cloud:

• Oracle database cloud service. 
• Oracle database cloud service bare metal. 
• Oracle Database Exadata Cloud Service. 
• Oracle Database Exadata Cloud at Customer. 
• Oracle Database Exadata Express Cloud Service – Managed. 
• Oracle Database Schema Cloud Service – Managed.

 

Ques: 14. How can I try oracle cloud for free?
 

Answer:

Oracle cloud offers $300 in free credits. You can make use of this credit for building your test, development, production databases, compute options, containers, IoT, bigdata, API, chatbots, integrations and lots more that are valid for 30 days. Create a free account and try these features for free.

 

Ques: 15. How is internal communication between PSM and Oracle cloud compute nodes established?
 

Answer:

SSH key-value pairs are used for this communication in port 22. This key value pair is specific to each database deployment and used for internal communication purposes. This SSH is internal to Oracle and not accessible. If there is an issue here PSM communication with compute node fails.

  

Ques: 16. Who has access to the SSH keys used for PSM communication with compute nodes?
 

Answer:

Only owner of that project has this access. For security purposes even oracle support and operations are not granted this access unless explicitly shared by customers for troubleshooting purposes.

 

Ques: 17. Give some invalid REST API operations for DNS Zone in OCI:
 

Answer:

The below REST API operations are invalid in OCI:

a) ListZones.
b) GetZone.
c) CreateZone.
d) UpdateZone.
e) DeleteZone.

 

Ques: 18. In TCP level health check you send requests to the backend servers at a specific URL. How will you validate the response?

Answer:

a). based on status code.
b). based on entity data returned.
c). based on entity body returned.

Ques: 19. What load balancer components that are mandatory backend set components?

Answer:

a). Load balancing policy.
b). List of backend servers.
c). Health check policy.
d). TCP handling.
 

Ques: 20. What configurations must be made for the process of designing a load balancer to accept incoming traffic?

Answer:

a). Listener must be configured.
b). A certificate must be available.
c). A security list that is open on a listener port must be available.

Top 20 Oracle RDMS Interview Questions and Answers

Ques: 1. What is an Index? Explain the different types of index.

Answer: 

An index is a performance enhancement method that allows faster retrieval of records from the table. An index creates an entry for each value thus making data retrieval faster.
While creating an index, we should remember the columns which will be used to make SQL queries and create one or more indexes on those columns.
Following are the available indexes.

a. Clustered index:

It sorts and stores the rows of data in the table or view, based on its keys. These are the columns included in the index definition. There can be only one clustered index per table because sorting of data rows can be done only in one order.

b. Non-clustered index:

It contains the non-clustered index key value and each key value entry, in turn, has a pointer to the data row. Thus a non-clustered index contains a pointer to the physical location of the record. Each table can have 999 non-clustered indexes.

c. Unique Index:

This indexing does not allow the field to have duplicate values if the column is unique indexed. It can be applied automatically when a primary key is defined.

 

Oracle Fusion Applications interview Questions and Answers

Ques: 2. What are Constraints? Explain the different Constraints available in SQL?

 Answer: 

These are the set of rules that determine or restrict the type of data that can go into a table, to maintain the accuracy and integrity of the data inside the table. Following are the most frequent used constraints, applicable to a table:

• <NOT NULL> It restricts a column from holding a NULL value. It does not work on a table.
• <UNIQUE> It ensures that a field or column will only have unique values. It is applicable to both column and table.
• <PRIMARY KEY> uniquely identifies each record in a database table and it cannot contain NULL values.
• <FOREIGN KEY> It is used to relate two tables. The FOREIGN KEY constraint is also used to restrict actions that would destroy links between tables.
• <CHECK CONSTRAINT> It is used to restrict the value of a column between a range. It performs a check on the values, before storing them into the database. It’s like condition checking before saving data into a column.
• <DEFAULT> It is used to insert a default value into a column.

Oracle Accounts Payables Interview Questions and Answers

 

Ques: 3. What are Triggers? What are its benefits? Can we invoke a trigger explicitly?

Answer: 

The trigger is a type of stored program, which gets fired automatically when some event occurs. We write a Trigger as a response to either of the following event:

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
• SQL allows defining Trigger on the table, view, schema, or database associated with the event.

Following are its benefits:

• Generating some derived column values automatically.
• Enforcing referential integrity.
• Event logging and storing information on table access.
• Auditing.
• Synchronous replication of tables.
• Imposing security authorizations.
• Preventing invalid transactions.

It is not possible to invoke a trigger explicitly. It gets invoked automatically if an event gets executed on the table having an association with the trigger.

Oracle ADF Interview Questions and Answers                                 

 

Ques: 4. What is the purpose of isolation levels in SQL?

Answer: 

Transactions use an isolation level that specifies the extent to which a transaction must be isolated from any data modifications caused by other transactions. These also help in identifying which concurrency side-effects are permissible.

Please refer the below list for more clarity on the different type of levels.

i. Read Committed.

It ensures that SELECT query will use committed values of the table only. If there is any active transaction on the table in some other session, then the SELECT query will wait for any such transactions to complete. Read Committed is the default transaction isolation level.

ii Read Uncommitted.

There is a transaction to update a table. But, it is not able to reach to any of these states like complete, commit or rollback. Then these values get displayed (as Dirty Read) in SELECT query of “Read Uncommitted” isolation transaction.

iii. Repeatable Read.

This level doesn’t guarantee that reads are repeatable. But it does ensure that data won’t change for the life of the transaction once.

iv. Serializable.

It is similar to Repeatable Read level. The only difference is that it stops Phantom Read and utilizes the range lock. If the table has an index, then it secures the records based on the range defined in the WHERE clause (like where ID between 1 and 3). If a table does not have an index, then it locks complete table.

v. Snapshot.

It is similar to Serializable isolation. The difference is that Snapshot does not hold a lock on a table during the transaction. Thus allowing the table to get modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data. In case any data modification happens in other sessions then existing transaction displays the old data from Tempdb.

Oracle Access Manager Interview Questions and Answers

 

Ques: 5. How do we Tune the Queries?

Answer: 

Queries can be tuned by Checking the logic (table joins), by creating Indexes on objects in the where clause, by avoiding full table scans. Finally use the trace utility to generate the trace file, use the TK-Prof utility to generate a statistical a nalysis about the query using which appropriate actions can be taken. 

Oracle Fusion HCM Interview Questions and Answers

Ques: 6. What is the difference between BEFORE and AFTER in Database Triggers?

Answer: 

BEFORE triggers, are usually used when validation needs to take place before accepting the change. They run before any change is made to the database. Let’s say you run a database for a bank. You have a table accounts and a table transaction. If a user makes a withdrawal from his account, you would want to make sure that the user has enough credits in his account for his withdrawal. The BEFORE trigger will allow to do that and prevent the row from being inserted in transactions if the balance in accounts is not enough.

Oracle SCM Interview Questions and Answers

AFTER triggers, are usually used when information needs to be updated in a separate table due to a change. They run after changes have been made to the database (not necessarily committed). Let’s go back to our back example. After a successful transaction, you would want balance to be updated in the accounts table. An AFTER trigger will allow you to do exactly that.

 

More on Oracle:

 Oracle Financials Interview questions and Answers

Oracle Cloud Interview Questions and Answers

Oracle PL/SQL Interview Questions and Answers

Oracle SQL Interview Questions and Answers

BI Publisher Interview Questions and Answers

Oracle 10g Interview Questions and Answers