Skip to main content

learning advanced SQL deeply

If you are well aware with the basic SQL, then learning advanced SQL is not a rocket science. I am sharing the topics for advanced SQL with a little description, you can go through the links which are referred by me also for learning advanced SQL deeply.
Advanced SQL Topics -
a. Stored Procedures - Stored Procedures are stored as a group in RDBMS and it is a collection of SQL statements. We can reuse it whenever we require in programming. The major benefit of the Stored Procedure is that it provides a security layer between the user interface and database. You can learn this with the help of syntax and example.
b. Indexes - Indexes used to speed up data retrieval in the database. It is a schema object. It slows down data input with insert and update statements, but speed up select queries and where clauses. Indexes are of following types -
  • Normal Index
  • Unique Index
  • Clustered Index
  • Non-Clustered Index
  • Function-Based Index
  • Composite Index
  • B-Tree Index
  • Bit Map Index
c. Normalization and Normal Forms - A process of organizing or restructuring the data in the database with a series is known as normal forms. It improves Data Integrity i.e. its dependency and reduces the redundancy. There are different types of Normal Forms in the database -
  • First Normal Form(1NF)
  • Second Normal Form (2NF)
  • Third Normal Form(3NF)
  • Boyce-Codd Normal Form (BCNF)
  • Elementary Key Normal Form(EKNF)
  • Fourth Normal Form(4NF)
  • Essential Tuple Normal Form(ETNF)
Refer the link to learn more about Normalization
d. Constraints - Constraints are the rules that applied to table columns for storing sensible data. It prevents to store invalid data. We can create constraints on single or multiple columns of any table. Following types of Data Constraints
  • Primary Key
  • Foreign Key
  • Check Constraints
  • Unique Constraints
  • Not Null Constraints
  • Informational Constraints
e. SQL Case - There are two types of format in SQL case. Simple Case and Searced Case. Simple Case where we compare an expression to a set of simple expressions for determining the result, that is specified in WHEN clause. Whereas, in Searched Case we compare a Boolean expression to logical conditions for determining the results.
f. SQL Auto Increment - It allows a unique number that is generated automatically, whenever a new record is inserted into a table.
g. SQL UNION - It used to combine the results of select statements without returning any duplicate rows. For using this Union Clause, Select Statement must have the same number of selected columns and expressions. Also same data type in the same order.
h. Triggers - Trigger a kind of stored procedures, that automatically executes when an event occurs in the database. triggers can be written for auditing, avoiding invalid transactions, security authorizations.
i. Materialized Views - It is a database object contains Query result. The process of setting materialized views is called as materializing. Learn more about Materialized Views.
So, these are the main topics in advanced SQL. If you like the explanation, please UPVOTE.
Labels:

Comments

Post a Comment

Popular posts from this blog

Class Weights for Handling Imbalanced Datasets

In scikit-learn, a lot of classifiers come with a built-in method of handling imbalanced classes. If we have highly imbalanced classes and have not addressed it during preprocessing, we have the option of using the class_weight parameter to weight the classes to make certain we have a balanced mix of each class. Specifically, the balanced argument will automatically weigh classes inversely proportional to their frequency. import numpy as np import pandas as pd import seaborn as sns import warnings from imblearn.over_sampling import SMOTE from imblearn.pipeline import make_pipeline from pylab import rcParams from sklearn.linear_model import LogisticRegression from sklearn.metrics import accuracy_score from sklearn.metrics import precision_score , recall_score from sklearn.metrics import f1_score , roc_auc_score , roc_curve from sklearn.model_selection import train_test_split from sklearn.model_selection import GridSearchCV In [2]:...
Post a Comment
Read more

Darts: Time Series Analysis made easy

  Dart: Sci-kit learn for Time Series Analysis It comprises all the API required for Time series analysis and its primary goal is to simplify the time series machine learning experience. Darts in not inbuilt, so install it using " pip install u8darts" In [4]: import pandas as pd from darts import TimeSeries Like pandas has DataFrame, Similarly, Darts has TimeSeries In [7]: df = pd . read_csv ( '../input/air-passengers/AirPassengers.csv' ) Series = TimeSeries . from_dataframe ( df , 'Month' , '#Passengers' ) This timeseries is univariate, containing only one variable. Splitting the series into training and validation TimeSeries In [9]: train , val = Series . split_before ( pd . Timestamp ( '19580101' )) Models All the time series models: Exponential smoothing, ARIMA & auto-ARIMA, Facebook Prophet, Theta method, FFT (Fast Fourier Transform), Recurrent neural networks (vanilla RNNs, GRU, and LSTM variants), Temporal Convol...
Post a Comment
Read more