Posts

Showing posts from February, 2023

MySQL Interview Questions Part - 1

Image
MYSQL INTERVIEW QUESTIONS  1) What is MySQL? MySQL is a very popular open-source relational database management system. To add, access, and process data stored in a database. 2) Difference between SQL and MySQL SQL MYSQL SQL is a query programming language that manages RDBMS. MySQL is a relational database management system that uses SQL. SQL is primarily used to query and operate database systems. MySQL allows you to handle, store, modify and delete data and store data in an organized way. 3) Features of MySQL Easy to use Secure Open source 4) Default port number of MySQL?          The   MySQL Server's default port is 3306 5) What is Primary key? The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key. 6) What is Foreign key? It is like a child table. It refers only to primary key column values from another table.  ...

Pandas DataFrame

Image
DATAFRAME: DataFrame is a two-dimensional array.  It's like a table with rows and columns. WAYS TO CREATE A DATAFRAME: DataFrame using list DataFrame using dictionary DataFrame using series DATAFRAME USING LIST:      Creating a dataframe with the list values.      Example 1:  import  pandas as pd  list_data  = ['pandas', 'numpy', 'pytest', 'pywin32'] result = pd.DataFrame( list_data ) print (result)                Example 2:  import   pandas as pd  list_data   = [['Vetri',20],['Vishal',21],['Jack',18]] result = pd.DataFrame( list_data , columns=['Name', 'Age'] ) print (result)         DATAFRAME USING DICT OF ARRAY/LIST:     Creating a dataframe with the dict of array/list values.      Example:     import  pandas as pd  data  = {'Name': ['Vetri', 'Vishal', 'Jack', 'Ram'...

Pandas Data Structure

Image
  DATA STRUCTURES IN PANDAS:      One of the most important things in Pandas is Data Structure.      Pandas have three data structures: Series DataFrame Panel SERIES: Series is a one-dimensional array. It holds any type of data.  WAYS TO CREATE A SERIES: Series using list Series using array Series using dictionary SERIES USING LIST:      Creating a series with the list values.      Example:     import pandas as pd  list_data = ['pandas', 'numpy', 'pytest', 'pywin32'] result = pd.Series( list_data ) print (result)         SERIES USING ARRAY:      Creating a series with the array values.      Example:     import  pandas as pd  import numpy as np array_data  =  np. array (['pandas', 'numpy', 'pytest', 'pywin32']) result = pd.Series( array_data ) print (result)         SERIES USI...

Introduction to Pandas in Python

Image
  WHAT IS PANDAS? Pandas is an Open Source Python package . It used to work with data sets. Mostly used for Data Analysis and Machine Learning tasks. The name Pandas have derived from the word '' Panel Data " which means Econometrics from multidimensional data.  The name Pandas is also a reference to " Python Data Analysis " Pandas is developed by Wes McKinney  in 2008 . FEATURES: High-Performance Data Analysis Tool. Works with large data sets easily. Support or load files with different formats. Represents the data in Tabular format [Rows and Columns]. Works on missing data. Indexing - Slicing - Subsetting the large data sets. Merge and Join the two different data sets easily. Pivoting and Reshaping data sets. BENEFITS: Data Representation. Shorten the procedure of handling data. Efficiently handle large data. More flexible and customizable. An extensive set of features. Thanks for choosing our blog 😊. 

Student database using MySQL Part 1

Image
MYSQL EXAMPLE     Will practice with an example. Will have an example for the Student database. CREATE DATABASE:     Create a database to store the tables.       Will create a Student database to store the details about the Students.           CREATE DATABASE Students; SHOW DATABASE:      The  SHOW DATABASES  statement lists all the databases. USE DATABASE:      The USE DATABASE statement is used to choose the database as a current database.      USE Students;      This statement makes the Student database a current database. CREATE TABLE: Create a new table in the Students Database.  Create department and Student tables.  The id column should be NOT NULL and AUTO_INCREMENT. And it is a Primary key. In the department table, the id column is the Foreign key for the Student table.           CREATE TABLE D...

Student database using MySQL Part 5

Image
EXAMPLE FOR JOINS           Will have an example to combine one or more tables.   SELF JOIN:           SELECT stud1.name AS Student1, stud2.name AS Student2, stud1.district FROM Students stud1, Students stud2 WHERE stud1.rollno <> stud2.rollno AND stud1.district = stud2.district ORDER BY stud1.district;     Create two Alias for the Student table - stud1 and stud2.     This query returns the name of the Student and district if the students are in the same district. Note:  [<> operator is used for not equal condition] INNER JOIN:      SELECT Students.name, Department.hod, Department.name FROM Students INNER JOIN Department ON Students.department=Department.id;      Returns records that have matching values in both tables. CASE: The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement...

Student database using MySQL Part 3

Image
EXAMPLE FOR MYSQL CLAUSE          Will have an example for MySQL clauses. WHERE  CLAUSE :      SELECT * FROM Students WHERE department=3;     Query filter the records from the Students table based on the department. ORDER BY:      SELECT * FROM Students ORDER BY district DESC;     Sort the result set in descending order. LIMIT:      SELECT * FROM Students LIMIT 2;     This query returns only 2 records from the Students table. GROUP BY:     SELECT COUNT(name), department FROM Students GROUP BY department;     Query group the rows that have the same department.     It returns the count of the students in the same department.      GROUP BY groups the records based on the department and it counts the name of the student based on the grouped department data. HAVING:      SELECT COUNT(name), department FROM Students GR...

Student database using MySQL Part 4

Image
EXAMPLE FOR MYSQL OPERATORS           Will have an example to see how the operators work with the table records.   AND, OR, NOT OPERATORS:      SELECT * FROM Students WHERE department=2 OR district='Madurai';     It returns the Student records if the student department is 2 or the student has a district in Madurai. It returns True if any of the conditions are satisfied.      SELECT * FROM Students WHERE department= 2 AND district='Madurai';        It returns the Student records if the student department is 2 and the student has a district in Madurai. It returns True if both conditions are satisfied.      SELECT * FROM Students WHERE NOT district='Madurai' ORDER BY district DESC;        It returns all the Student records except the student who has a district in Madurai.     The queries filter the records from the Student table based on th...

Student database using MySQL Part 2

Image
EXAMPLE FOR MYSQL TABLE MODIFICATIONS          Will work with an example to modify the table structure.   DESCRIBE TABLE:     The Describe statement is used to describe the structure of the table.      DESCRIBE Students;   UPDATE  QUERY AND SELECT QUERY : An update query is used to update the data in the table. The select query returns all the data in the Students Table.           SELECT * FROM Students;           UPDATE Students SET age= 18 WHERE name = 'Vetri' ALTER TABLE:      Alter statement is used to modify the table structure.      ALTER TABLE Students ADD DateOfBirth date;      ALTER TABLE Students MODIFY COLUMN DateOfBirth year;      ALTER TABLE Students DROP COLUMN DateOfBirth; DELETE TABLE:      DELETE FROM Students WHERE district='Coimbatore';     This...