Follow by Email

SQL Basic Questions & Answers


What is SQL?
SQL is Structured Query Language. It is the database computer declarative language used for managing data in the relational database management system.

What is RDBMS?
RDBMS is relational database management system. In this data is structured in tables, fields & records. Each table contains database table rows & each row contains one or more database table fields (columns).
e.g. : SQL , Oracle ,MYSQL

What is DBMS?
Database Management System is software package which controls the creation, maintenance & use of the data.

Difference between DBMS & RDBMS

DBMS
RDBMS
1
No relationship Concept.
Relation concept between two database tables.
2
Only one user supported.
Multiple users supported.
3
Data is stored in files.
Data is stored in tables.
4
e.g.: FoxPro ,IMS
SQL ,Oracle

12 rules of CODD for RDBMS (relational database management system).

  1. Foundation Rule
    A relational database management system must manage its stored data using only its relational capabilities.
  2. Information Rule
    All information in the database should be represented in one and only one way - as values in a table.
  3. Guaranteed Access Rule
    Each and every datum (atomic value) is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.
  4. Systematic Treatment of Null Values
    Null values (distinct from empty character string or a string of blank characters and distinct from zero or any other number) are supported in the fully relational DBMS for representing missing information in a systematic way, independent of data type.
  5. Dynamic On-line Catalog Based on the Relational Model
    The database description is represented at the logical level in the same way as ordinary data, so authorized users can apply the same relational language to its interrogation as they apply to regular data.
  6. Comprehensive Data Sublanguage Rule
    A relational system may support several languages and various modes of terminal use. However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and whose ability to support all of the following is comprehensible:
    1. data definition
    2. view definition
    3. data manipulation (interactive and by program)
    4. integrity constraints
    5. authorization
    6. transaction boundaries (begin, commit, and rollback).
  7. View Updating Rule
    All views that are theoretically updateable are also updateable by the system.
  8. High-level Insert, Update, and Delete
    The capability of handling a base relation or a derived relation as a single operand applies nor only to the retrieval of data but also to the insertion, update, and deletion of data.
  9. Physical Data Independence
    Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representation or access methods.
  10. Logical Data Independence
    Application programs and terminal activities remain logically unimpaired when information preserving changes of any kind that theoretically permit unimpairment are made to the base tables.
  11. Integrity Independence
    Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and storable in the catalog, not in the application programs.
  12. Distribution Independence
    The data manipulation sublanguage of a relational DBMS must enable application programs and terminal activities to remain logically unimpaired whether and whenever data are physically centralized or distributed.
  13. Nonsubversion Rule
    If a relational system has or supports a low-level (single-record-at-a-time) language, that low-level language cannot be used to subvert or bypass the integrity rules or constraints expressed in the higher-level (multiple-records-at-a-time) relational language. 
Referances:
http://www.cse.ohio-state.edu/~sgomori/570/coddsrules.html

How to show multiple columns in the single dropdown list using sqlreader?

Here i will show you how can u bind the multiple columns ti the single dropdown list..

Steps:
Add dropdown list from toolbox.
Now bind the data at the page load event of the page.

protected void Page_Load(object sender, EventArgs e)
    {
       string connectionString = 
"Data Source=VRAKSH-VAIBHAVS;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=data#123";
       SqlConnection con = new SqlConnection(connectionString);
       SqlCommand cmd = new SqlCommand 
("select City + ' ' + PostalCode as PCity from Person.Address", con);
       con.Open();
       SqlDataReader rd;
       rd = cmd.ExecuteReader();
        DataSet ds = new DataSet();
        DropDownList1.DataSource = rd;
        DropDownList1.DataValueField = "PCity";
       DropDownList1.DataBind();
        con.Close();
    }
 
 
 Remember to bind the data source to the dropdown list .....
& also bind the column to the drop down list like i did i.e.
DropDownList1.DataValueField = "PCity"
 
Like this you can bind multiple columns to the dropdown list .... 
 

"Maximum request length Exceeded" error while uploading files in normal & also in umbraco

You may get error that "Maximum request length Exceeded" while uploading the files of bigger size...
By default we can upload file having size less than 4mb .

To change  this .... do this things....

Please put this your web.config file inside the <system.web>
<httpRuntime requestValidationMode="2.0" maxRequestLength="102400" executionTimeout="3600" />
Where maxRequestLength is the length of the file in kb.
 
&
Add this inside  your <system.Webserver>
    <security>

      <requestFiltering>

        <requestLimits maxAllowedContentLength=" 1048576000"/>

      </requestFiltering>

    </security>
Where maxAllowedContentLength is length of the file in the bytes

By setting this you will be allowed to upload files upto 100mb …

If you want to upload files greater than 100mb then just change maxRequestLength
& maxAllowedContentLength

Converting normal textbox into password textbox on clicking on the textbox

In this tutorial i will show how can u show the text in the textbox which is having TextMode=password but when clicked on that textbox it converts it to the password textbox.
Steps:
Take one textbox & set its TextMode to password.
now go to source mode of visual studio write this in the textbox

onfocus="if(this.value =='PASSWORD')this.value='';" onblur="if(this.value==''){this.value='PASSWORD';this.TextMode='SingleLine';}"
After adding this what happens is .....
It will initially show text "PASSWORD".
After that when you click on it ..it will be converted password textbox which will show text as "*"

your textbox markup should look something like this....

 < asp:TextBox ID="Password" runat="server" TextMode="Password"
onfocus
="if(this.value =='PASSWORD')this.value='';" onblur="if(this.value==''){this.value='PASSWORD';this.TextMode='SingleLine';}">PASSWORD< /asp:TextBox>

How to add icon/image to the browser tab ?

In this tutorial i will show you how can we add the icon at the browser tab in the website like other sites.

We just have to do is that take image you want & put it inside your project ....
then just type this mark up in side tag your website template.

<link rel="shortcut icon" href="/images/iconGif.gif">
just change the path with your image.

Like this you can add icon/image to your browser.

If you want background transparency then use .png or .gif image.
If you use .jpg iamge then
background transparency will be avoided.

All browser excepts this type of images i.e. (.png,.gif,.jpg) .....but internet explorer will con consider this images ...to work with internet explorer browser icon images should be .ico i.e. icon file.

then you will write markup  something like this....

      <link rel="shortcut icon" href="/images/favicon.ico" />
like this your icon will be shown in every browser.

If you want to convert ur .png file to .ico file then go to this web site ....

http://www.convertico.com/

How to show large image on mouse click on the small image using JavaScript in asp.net ?

In this tutorial I will show you how we can use java script to display the large image by clicking on the small image.

Steps:

Create new website.

Now add one more div tag in the body ....

<div id="bigDiv" style="position: absolute; z-index: 2; border: solid 1px black;

width: auto; height: auto; left: 250px;">

Drag the image control in the body of the form (also set the attribute ImageUrl to some image in the project )& place it after the above div tag.

Now add the following javascript in the head of the page.

<script type="text/javascript" language="javascript">
 
        function showZoomImage(obj) {
            document.getElementById("bigDiv").style.visibility = "visible";
            document.getElementById("bigDiv").innerHTML = "";
            document.getElementById("bigDiv").style.left = event.clientX;
            document.getElementById("bigDiv").style.top = event.clientY;
           document.getElementById("bigDiv").style.zIndex = "0";
 
        }
        function ShowDefaultImage(obj) {
            document.getElementById("bigDiv").innerHTML = "";
            document.getElementById("bigDiv").style.visibility = "hidden";
        }
 
 
   script>
 
 

This will look something like this.....

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" 
Inherits="_Default"EnableViewState="false" ViewStateMode="Disabled" %>
 
<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>title>
    <script type="text/javascript" language="javascript">
 
        function showZoomImage(obj) {
            document.getElementById("bigDiv").style.visibility = "visible";
            document.getElementById("bigDiv").innerHTML = "";
            document.getElementById("bigDiv").style.left = event.clientX;
            document.getElementById("bigDiv").style.top = event.clientY;
            document.getElementById("bigDiv").style.zIndex = "0";
 
        }
        function ShowDefaultImage(obj) {
            document.getElementById("bigDiv").innerHTML = "";
            document.getElementById("bigDiv").style.visibility = "hidden";
        }
 
    script>
head>
<body>
    <form id="form1" runat="server" enableviewstate="false" viewstatemode="Disabled">
    <div>
        <div id="bigDiv" style="position: absolute; z-index: 2; border: solid 1px black;
            width: auto; height: auto; left: 250px;">
        div>
<asp:Image ID="Image2" runat="server" ImageUrl="~/Images/Desert.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />
        div>
    form>
body>
html>
 

Just keep in mind that id of the empty div & id in the script should be the same (i have indicated in the Green colour in the above mark-up)

Like this we can make much more things ....I have made this.....

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default"
    EnableViewState="false" ViewStateMode="Disabled" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>title>
    <script type="text/javascript" language="javascript">
 
        function showZoomImage(obj) {
            document.getElementById("bigDiv").style.visibility = "visible";
            document.getElementById("bigDiv").innerHTML = "";
            document.getElementById("bigDiv").style.left = event.clientX;
            document.getElementById("bigDiv").style.top = event.clientY;
            document.getElementById("bigDiv").style.zIndex = "0";
 
        }
        function ShowDefaultImage(obj) {
            document.getElementById("bigDiv").innerHTML = "";
            document.getElementById("bigDiv").style.visibility = "hidden";
        }
 
 
    script>
    <style>
        .oneImage
        {
            padding: 5px 5px 5px 5px;
        }
        .allImage
        {
            padding: 0px 10px 0px 10px;
            width: 200px;
            height: 605px;
            overflow: scroll;
        }
    style>
head>
<body>
    <form id="form1" runat="server" enableviewstate="false" viewstatemode="Disabled">
    <div>
        <div id="bigDiv" style="position: absolute; z-index: 2; border: solid 1px black;
            width: auto; height: auto; left: 250px;">
        div>
        <div class="allImage">
            <div class="oneImage">
                <asp:Image ID="Image2" runat="server" ImageUrl="~/Images/Desert.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />div>
            <div class="oneImage">
                <asp:Image ID="Image1" runat="server" ImageUrl="~/Images/Chrysanthemum.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />div>
            <div class="oneImage">
                <asp:Image ID="Image3" runat="server" ImageUrl="~/Images/Hydrangeas.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />div>
            <div class="oneImage">
                <asp:Image ID="Image4" runat="server" ImageUrl="~/Images/Jellyfish.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />div>
            <div class="oneImage">
                <asp:Image ID="Image5" runat="server" ImageUrl="~/Images/Lighthouse.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />div>
            <div class="oneImage">
                <asp:Image ID="Image6" runat="server" ImageUrl="~/Images/Desert.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />div>
            <div class="oneImage">
                <asp:Image ID="Image7" runat="server" ImageUrl="~/Images/Chrysanthemum.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />div>
            <div class="oneImage">
                <asp:Image ID="Image8" runat="server" ImageUrl="~/Images/Hydrangeas.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />div>
            <div class="oneImage">
                <asp:Image ID="Image9" runat="server" ImageUrl="~/Images/Jellyfish.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />div>
            <div class="oneImage">
                <asp:Image ID="Image10" runat="server" ImageUrl="~/Images/Lighthouse.jpg" Width="100"
                    Height="100" onclick="showZoomImage(this);" />div>
        div>
    form>
body>
html>