Categories
SQL

Basic SQL Commands to work with Tables | SQL Tutorial for Beginners

Below are some of the basic SQL statements which we use in day to day work.

Create Table
To create a new Table in Data Base use below syntax

create table table_name (
column1 datatype,
column2 datatype,
....
);

Example :-

create table onlineqa (empno number(4),ename varchar2(10),dept varchar2(9));

Drop or Delete a Table
To Drop an existing table from Database. Dropping a table from database deletes the table completely with structure from database.
If you want to delete the Rows from the entire table use the Truncate command instead.

drop table table_name;

Example :-

drop table onlineqa; 

Truncate a Table
To delete or remove all the rows from the a table we use the Truncate command. Truncate command will not delete the Table itself, it will remove data inside a table.

truncate table table_name;

Example :-

truncate table onlineqa; 

Alter a Table
To Add a column or Delete a column or Modify a column in an existing Table we use Alter command.
Alter table to Add Column

Alter Table table_name
Add column_name datatype; 

Example :-

Alter Table onlineQA
Add empName VARCHAR(30); 

Alter table to Delete or Drop a Column

Alter Table table_name
drop column column_name

Example :-

Alter Table onlineQA
drop column empName; 

Alter table to Modify an Existing Column

Alter Table table_name
Modify column_name datatype; 

Note:- In case of Sql server use “Alter” instead of “Modify”.
Example :-

Alter Table onlineQA
Modify column empName Varchar2(40); 

 

Categories
SQL

Different Types of SQL Statements in MySQL | SQL Commands

To Interact with Databases, we need some way of communication, which Database can understand. For this purpose we have some SQL Commands or Statements defined like Select, Create, Delete, Commmit etc…

Based on these commands characteristics or behavior they are grouped into different categories. However, we will be using mostly below 3 categories to interact with Database in our day to day life.

There are mainly three types of SQL Statements?Commands available in Mysql. They are,

  1. Data Definition Statements
  2. Data Manipulation Statements
  3. Transaction Control Statements

Data Definition Language statements (DDL):

These statements are used to define the structure of the table or database.

Data Definition statements are Create, Alter, Drop, Truncate and Rename.

Data Manipulation Language Statements (DML):

These statements are used to change the data present in table but not the structure of the table.

Data Manipulation Statements are Select, Insert, Delete and Update.

Transaction Control Statements (TCL):

Any DML change on a table is not a permanent one. We need to save the DML changes in order to make it permanent. Transaction control statements manage changes made by DML statements.

The transaction control statements are commit, rollback and Save point.

As a Developer or tester we mostly use above statements. Apart from above 3 categories, we have some other categories also available for advanced users like Database Administration Statements , MySQL Utility Statements, Replication Statements  etc.. to name few.

You can find more details about SQL Statements and their syntax for MySQL server @ SQL Statement Syntax.

Feel Free to add more points are doubts in the comments below.

Categories
SQL

Important Topics in SQL for Manual Testers

Below are the list of all the important topics that are important for a manual tester in SQL. And also they are very important for any level Manual Testing Job interviews

1. Types of statements

2. Data Definition statements(DDL)

  • Alter Table
  • Create Database
  • Create Index
  • Create Table
  • Drop Database
  • Drop Index
  • Drop Table
  • Rename Table
  • Truncate Table

3. Data Manipulation Statements(DML)

  • Delete Table
  • Insert
  • Select
  • Update

4. Transaction control statements(TCL)

  • Commit
  • Save point
  • Rollback

5. Functions

  • Aggregate functions
  • Scalar Functions

6. Joins

  • Inner join
  • Left Join
  • Right Join

7. Union

8. ‘And’ and ‘Or’ operators

9. Order By

10. LIMIT

11. Group By

12. Wild Cards

13. Distinct

14. Like

15. In

16. Between

17. Aliases

18. Insert into select(for copy)

19. Constraints

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • DEFAULT

20. Index

21. Auto Increment

22. Views

23. Dates

24. Null values

25. Null Functions

26. Data Types

27. Having

These are some of the important topics regarding MySQL…

Let us see each topic in-detail in next posts…

 

 

 

 

Categories
SQL

Introduction to SQL using MYSQL | SQL Tutorials

SQl is the structured query language.

It is one of the language used for accessing databases.

A database is the collection of Tables.

A table consists of rows and columns.

The intersection of a row and a column is called a cell.

RDBMS is one type of database in which data is stored in the form of tables.

RDBMS means Relational Database Management System.

SQL is not case sensitive.

There are several database management systems like SQL Server, MYSQL,Oracle,Sybase,DB2 etc.

In these SQL tutorials I use MYSQL …..

Now lets start with the following topics:

1. Create database

2. List of all databases

3. Delete a database

4. Use a particular database

5. Rename Database

1. Create Database:

To create a database we use the following syntax.

Syntax: CREATE  DATABASE  databasename;

2. List of all databases:

To show the list of all databases use the following syntax.

Syntax: SHOW DATABASES;

3. Delete a database:

To delete a database use the following syntax:

Syntax: DROP  DATABASE  databasename;

4. Use a database:

To use a particular database use the following syntax.

syntax: USE databasename;

After this query what ever actions we perform on the database it will be reflected on this database.

5.Rename database:

To change the name of a database we can use the following syntax in MySQL.

syntax: RENAME DATABASE  oldname  TO  newname;

But this is not working in newer versions as they have removed this because renaming can cause loss of data. So to rename a database you should create new data base with the new name and load the database from old data base and delete the old DB

These are the operations that can be done on a database…….

Let us see the important topics in MySQL for Test Engineers in next post….

 

 

 

 

 

 

 

Categories
Manual Testing SQL

Questions asked in Manual Testing interview

I would like to share real time Manual Testing interview Questions asked in for 1.5 years of experience…

1. what is the difference between smoke testing and sanity testing?

2. what are the different testing methodologies ?

3. Which methodology is used in your project?( I told agile methodology)

4. Tell me the entire process of agile methodology with block diagram?

5. What is integration testing? what are the types of integration testing?

6. How can you test a field which have the values [0-9]?

7. When testing process starts in your project?

8. What have you done in your testing career?

9. What is the most challenging thing in your testing career and how you achieved it?

10. Can we use group by without where condition?(sql question) If yes how can we do that? ans is by using having

11. How can you test an application on non functional requirements?

12. What are different non functional testing?

13. Which tool used to log and track the bugs? (QC)

14. What is RTM ? How will you prepare RTM in QC?

15. What is the Windows version using for your testing?

These are the questions asked in the Manual Testing interview….

Thank u…..